Summary of a summary DAX

[Anonymous]

Hi

There is somethin I am trying to achieve and it looks like a summary of a summary. The issue is that when I do the second summary I don't know how to refer to the column created in the summary table, it tells me that it does not exist. So for example:

I have this table:

Day	Person	Shift	Money
Monday	Maria	Day	2
Monday	Anna	Night	4
Monday	Maria	Night	9
Tuesday	Edward	Day	4
Tuesday	Antoine	Night	3

And I create a summary table on day and shift to know the average Monay for that combination of day and shift.

Day	Shift	Average Money
Monday	Day	2
Monday	Night	6.5
Tuesday	Day	4
Tuesday	Night	3

So then I can get the final output that is the max money for a particular day (applyting for example a months period, you want to know the max money on any given day, which would be the MAXX of:

Day	Max Money
Monday	6.5
Tuesday	4

Return:

6.5

As I mentioned before, I think a summary of a summary seems an easy solution, but when I select the column created with the average money, it tells me it does not exist.

Any ideas? Please let me know if this is a bit too vague to understand.

Thanks in advance.

[Kumail]

Hello @Anonymous 

If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.

You can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 

Regards
Kumail Raza

[mahoneypat · Accepted solution]

Here is a measure expression you can use.  I added in the Day column too into the SUMMARIZE, so that you get the max day value in the total row too.

Day Max =
VAR vSummary =
    ADDCOLUMNS (
        SUMMARIZE ( T1, T1[Day], T1[Shift] ),
        "cAvg", CALCULATE ( AVERAGE ( T1[Money] ) )
    )
RETURN
    MAXX ( vSummary, [cAvg] )

Pat