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.
I have a matrix table that is a measure performing an averagex on a dataset. Resulting number and the total on the bottom of the matrix is calculating correctly.
The matrix column total seems to only perform in a summary fashion, and I was wondering if there is a trick to get it to actually give me the average for the row.
Any help would be appreciated.
Solved! Go to Solution.
Someone on another website was able to help me. All I had to do was modify my formula to divide it by the distinctcount of places, and it gives me the number I want in the column total without changing all of the other values.
Hope this is able to help someone in the future.
OLD FORMULA
Open Incidents - Average =
AVERAGEX(
'DATE',
CALCULATE (
COUNTROWS ( 'Open Incidents' )
)
)
NEW FORMULA
Open Incidents - Average - NEW =
DIVIDE(
AVERAGEX(
'DATE',
CALCULATE (
COUNTROWS ( 'Open Incidents' )
)
),
DISTINCTCOUNT( 'Open Incidents'[PLACE] ),
0
)
Hi @freshwave ,
Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.
Regards,
Frank
Hi Frank,
I was not able to share my file due to confidentiality and size reasons. I mocked up a very simple PBIX file that shows my issue. Hopefully you can give me some guidance.
https://1drv.ms/u/s!AnwFbg8GYO4JhASaL2bhyoYgbCro
Thanks,
Jeff
Someone on another website was able to help me. All I had to do was modify my formula to divide it by the distinctcount of places, and it gives me the number I want in the column total without changing all of the other values.
Hope this is able to help someone in the future.
OLD FORMULA
Open Incidents - Average =
AVERAGEX(
'DATE',
CALCULATE (
COUNTROWS ( 'Open Incidents' )
)
)
NEW FORMULA
Open Incidents - Average - NEW =
DIVIDE(
AVERAGEX(
'DATE',
CALCULATE (
COUNTROWS ( 'Open Incidents' )
)
),
DISTINCTCOUNT( 'Open Incidents'[PLACE] ),
0
)
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |