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!
For reference and learning, go here: Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn
You will need to include (but can later HIDE) base measure/calculations, which is why your reference above did not work.
Check out the RANGE function in Visual Calculations: RANGE function (DAX) - DAX | Microsoft Learn
They offer this example:
TotalSalesLast6Months = CALCULATE(SUM([SalesAmount]), RANGE(-5, Rows))
Use that to calculate both your numerator and denominator for the DIVIDE function. You will get 3 new columns. Hide the first two, and you are left with the DIVIDE function only.
At the start of your set of columns, the Visual Calculation above will not have access to a full 8 weeks, but it does handle the math correctly.
Notes: Learn about the AXIS option parameter for Visual Calcs. It can be ROWS or COLUMNS. It changes behavior. I think you would need COLUMNS.
Learn about the RESET optional paramter.
Good luck.
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!