Sum of averages in Matrix using variables in a measure

[Anonymous]

Hi, I'm sure there should be a simple solution to this, but I haven't managed to find it as yet.

I want to create a table where the averages calculated at a course level are summed at a subject, and at a Faculty level, as opposed to the calculation occurring at each level.

As an example, the data currently looks like this, where the first column is Year1 based on a measure, the second column Year2 based on another measure, and the third column is the average using a measure DIVIDE( ( [Year1] + [Year2] ), 2):

 The bold total in the third column is the Subject, with others being Courses within that Subject.  What I would like is to see the Subject total be 31 - the sum of the averages within the courses in the subject area - rather than the averages of the totals for the subject.

Any help much appreciated!

[Ashish_Mathur]

Hi,

Try this measure

=SUMX(VALUES(DATA[Courses]),[Measure 3])

Hope this helps.

[lbendlin]

Think like the Grand Total. Very often measures designed for the Grand Total will also work for the Row and Column Totals and for the individual cells.

Use SUMX over the current filter context. While that is overkill for the individual rows, it will still work, and it will also work for the row total.