PowerPivot Double aggregation. MAX for 2 variables then SUM for 1 variable.

[DavidBenaim]

I need to
1. Find the MAX of students on 2 variables. (course and country), 4 countries x 3 courses, so there are 12 MAX answers.
2. Filter the data to only include dates in say 2015 using a "timeline" slicer (not all)
3. SUM the results of step 1 filtered by step 2, this time, I want an aggregation by course ONLY.

I have managed to get this by using a Pivot table for step 1, then a SUMIF formula for step 3, but I want this expressed as a measure using DAX (rather than a SUMIF) so it can be charted using Pivot Charts & is more mobile. I have Excel 2016 Office 265 ProPlus version.

The situation is that the participants who come, get double counted as there is often session 1 of Maths course on Monday, session 2 on Tue, so to get the true number of participants on each course, we need the MAX number for each country, (this is essentially an aggregation step to get to what is normally the “real” data, as the raw data from the tabular SUMMED includes double counting so is meaningless). I want to therefore “hide from client tools” the option to choose SUM Participants directly so the users cannot make mistakes and choose a meaningless aggregation).

 

[Matt Allington]

You should only post your questions on one forum. Also you really need to provide some information about your data model, otherwise it is difficult to provide help.