Grouping and Aggregating a Table stored in a DAX Variable

DAX

Suppose we have 2 tables as follows

Grade1

SubjectMarks
Maths90
Physics80
Chemistry70

Grade2

SubjectMarks
Maths100
Physics88
Chemistry90

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

Grades_1&2

SubjectMarks
Chemistry160
Physics168
Maths190

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