Forum Discussion
Column Total Averages Not Quite Correct in Matrix
ToddChitt pankajnamekar25 Shahid12523 Ashish_Mathur
I was able to get each row to provide a simple average by doing the following:
TotalSales = SUM(Sales[Net Sales])
TotalMargin = SUM(Sales[GrossMargin])
Margin% = DIVIDE([TotalMargin],[TotalSales])
The measure that made the difference was this:
AVERAGEX(VALUES('Dates'[EOW]), [Margin%]))
It appears the issue was at least somewhat related to the non-additive measure issue (as much as I understand it). Wish there was a way to toggle that behavior in BI Desktop but at least it's working now. Thank you all so much for your suggestions and help, much appreciated. ToddChitt, many thanks for the pointers on the Visual Calcs. I'll be reading up on that much more for future use.
Cheers!
Your 8 Week Avg column is off because Power BI is averaging percentages instead of recalculating them using total margin and sales over the 8-week window. To fix it, create a measure that sums margin and sales over the full 8 weeks, then divides them—this gives a weighted average, not a simple mean. That’ll align your matrix totals with the actual department-level averages.
Shahid12523 The 8 week end of week dates in the matrix columns are controlled by two things - a calculated column in my date table which calculates the end of week date based on a 7 day work week - Sunday to Saturday. Then, I use that calculated column as a page level filter using relative dating set for the last 8 calendar weeks. The report users want to see the average margin percentage of those last 8 weeks. Basically, the same as you would see if you just averaged each row's percentages in Excel.