Forum Discussion
Matrix total not average
Visual is a matrix with Venues as rows and months as columns. Values are attendance totals. Measure calculates the Average monthly attendance.
Cells are accurately reflecting the average attendance for each event in the month (5 in the case of April). The column total (128) is incorrect and should be 512. I know it has to do with averaging averages, but can't find the right DAX formlua.
Here's a screen print of the matrix and a spreadsheet showing the raw data and hand calculated values expected...
Hi newhopepdx
please try
AvAtt =
SUMX (
VALUES ( 'tblAttendance'[Venue] ),
CALCULATE ( AVERAGE ( 'tblAttendance'[Attendance] ) )
)
6 Replies
Hi newhopepdx
please try
AvAtt =
SUMX (
VALUES ( 'tblAttendance'[Venue] ),
CALCULATE ( AVERAGE ( 'tblAttendance'[Attendance] ) )
)Elegant!
"VALUES", I was trying to configure something with SUMMERIZE and hadn't hit on the right arguments.
SUMMARIZE would work just fineand probably more efficient without the need for CALCULATE
AvAtt =
SUMX (
SUMMARIZE (
'tblAttendance',
'tblAttendance'[Venue],
"@Average", AVERAGE ( 'tblAttendance'[Attendance] )
),
[@Average]
)
newhopepdx First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
