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

[DavidBenaim]

I am looking 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.  3 answers only.

I have managed to get this in Excel 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 done in PowerBI or Excel using better charting. I have Excel 2016 Office 365 ProPlus version, PowerBI desktop and a PowerBI pro service licence.

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).

[MattAllington]

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.

[DavidBenaim]

Hi Matt

Thanks for the reply. I am actually in the process of reading your book on writing DAX coincidentally, enjoying it so far!

I really appreciate your help. I have taken a picture of a very simplified example of what I am trying to do. The yellow table is my data model (a very simplified version).

I need to find the MAX of course & country, then filter it for dates using a timeline. Table 2 (the Pivot shows this), then I need to find the SUM of the courses only, so the blue table (table 3) shows this. The formulas are SUMIFs on the Pivot Table.

If I go straight to trying to the SUM of Courses in a Pivot it gives me wrong answers (see Table 4 - another PivotTable).
I tried using Power Query group by, then splitting into 2 tables, but that means I cannot filter (as I lose the date info with filter).

Ideally, I would like to:
1. Create a measure which does the MAX on the two variables always (i.e. explicitly built into the formula) and "hide from client tools" other measures.
2. Create another DAX measure which then does what I do with SUMIF in table 3 but using DAX. I note again that a simple SUM doesn't work for this.

Sorry for posting on two different forums, I thought it would help me reach a wider audience.

Thanks!
David

[v-shex-msft]

Hi DavidBenaim ,

In your scenario, do you want to get the Table 3 result in Power BI desktop from the source table Table 1? If that is a case, which logic do you use to calculate data?

About Table2, you mean using MAX() function, how did you create this table? For example, the MAX of Students should be 57 instead of 39. Also please share more information about Table 3 and Table 4, so that we can understand it clearly.

Regards,

Xiaoxin Sheng

[DavidBenaim · Accepted solution]

Hello all

I discovered the right answer in the end, it is to create a measure that is:

=if(isfiltered(

                     FactByCountry[Course]) ,SUMX(
                                                                        VALUES(FactByCountry[Country])
,CALCULATE(

                     MAX(FactByCountry[Participants]) )))