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!
ToddChitt - Sounds intriguing but, so far, I can't seem to get the visual calcs to work for this matrix. That's probably due to my unfamiliarity with how they work. I've been trying to use the information here but it doesn't really provide enough detail (for me at least).
The requirements for this report are that the margin percentages are the actual percentages for each end of week date - the full business week is Sunday to Saturday, with the end of week date always being a Saturday. Then, each department row's percentages are to show a simple average, like you'd see in Excel. End of week dates come from a calculated column in my date table (EOW). Which dates are shown is controlled by using the EOW column set to show the last 8 calendar weeks. At the start of each new business week, the report is to automatically show the previous 8 week margin percentages. All of this is working exactly as it should except for the simple average for each department row.
Here's my initial attempt at usual the Visual Calculations function. None of the fx functions match what I need, at least as far as I can tell. Clearly, I'm missing something here but don't know what. I'll keep experimenting but it seems like getting a simple average of a row of numbers should be much simpler.
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.