Suppose we have 2 tables as follows

Grade1

Subject | Marks |
---|---|

Maths | 90 |

Physics | 80 |

Chemistry | 70 |

Grade2

Subject | Marks |
---|---|

Maths | 100 |

Physics | 88 |

Chemistry | 90 |

OUR DESIRED RESULT IS A CALCULATED TABLE USING DAX with output as follows

Grades_1&2

Subject | Marks |
---|---|

Chemistry | 160 |

Physics | 168 |

Maths | 190 |

We can use a Variable to combine the 2 tables first using UNION function in DAX and then summarize the Subjects using Summarize function

BUT

the problem is how can we aggregate the Marks.. The Aggregate functions like SUM,MAX,MIN,AVERAGE are not available for VARIABLES

The answer is we have to use an ITERATOR. Fortunately the beautiful “Summarize” function provides not only FILTER context but also ROW context to apply the ITERATOR functions

The final and correct formula looks like this

Grades_1&2 =

VAR temp =

UNION ( Grade1, Grade2 )

RETURN

SUMMARIZE (

temp,

[Subject],

“Marks”, SUMX ( FILTER ( temp, [Subject] = EARLIER ( [Subject] ) ), [Marks] )

)

Download the file from here