Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |