Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Summary of a summary DAX

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:

 

DayPersonShiftMoney
MondayMariaDay2
MondayAnnaNight4
MondayMariaNight9
TuesdayEdwardDay4
TuesdayAntoineNight3

 

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

 

DayShiftAverage Money
MondayDay2
MondayNight6.5
TuesdayDay4
TuesdayNight3

 

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:

 

DayMax Money
Monday6.5
Tuesday4

 

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.

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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.

 

mahoneypat_0-1629114576155.png

 

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

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
Kumail
Post Prodigy
Post Prodigy

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
Employee
Employee

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.

 

mahoneypat_0-1629114576155.png

 

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

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.