Forum Discussion
Helper IIIColumn Total Averages Not Quite Correct in Matrix
Greetings All -
I'm building a matrix which shows a moving 8 week gross margin percentage by store, department and end of business week date - first screenshot below. The individual percentages for each department and end of week date are showing correctly. However, the 8 Week Avg column is just slightly off, except for those rows where there is only 1 value for the entire 8 week period. For example, for the Clothing department, each end of week percentage value is correct, but the 8 week average for that departments row is off by .7% - it's supposed to be 45.7%. The second screenshot shows what the 8 week average is supposed to be for each department for this particular store. The 8 week average isn't off by a lot in most cases, though a few are off by almost as much as a full percentage point (Automotive, RV and Marine for example).
At first I thought this might be due to some cumulative rounding error because I'm rounding the sales and margin dollar values to the whole dollar, however, now I'm not sure. I suspect this may be related to that pesky additive/non additive measure issue. All store, department, sales and margin data are in a single table which has a one to many relationship with my date table. I'm using calculate(sum functions to get my total sales and gross margin values. Then, I'm using the measure Margin% = DIVIDE([TotalMargin],[TotalSales]) to get the total gross margin percentage. This is working great throughout other sections of the report, it's only this 8 week moving view where things are slightly off - and the only part that's off is the 8 Week Avg column. What am I missing?
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!
11 Replies
- Ashish_Mathur
Super User
Hi,
Share the download link of an MS Excel file with your Excel formulas written there. I will understand those and convert those into measures.
- Shahid12523
Community Champion
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.
rbowenShahid12523 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.
- pankajnamekar25
Hello rbowen
Try with below seperate measure
Total Sales =
SUM ( Sales[Net Sales] )
Total Margin =
SUM ( Sales[Gross Margin] )
Gross Margin % =
DIVIDE ( [Total Margin], [Total Sales] )
8 Week Gross Margin % =
VAR EndDate =
MAX ( 'Date'[Date] )
VAR StartDate =
EndDate - 56 // last 8 weeks (56 days)
RETURN
DIVIDE (
CALCULATE ( [Total Margin], DATESBETWEEN ( 'Date'[Date], StartDate, EndDate ) ),
CALCULATE ( [Total Sales], DATESBETWEEN ( 'Date'[Date], StartDate, EndDate ) )
)
If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.
Thanks,
Connect with me on:
LinkedIn- rbowen
Thank you pankajnamekar25 . Unfortunately, I get the same numbers as before using your DAX. The column dates in the matrix are end of week dates - the full business week going from Sunday to Saturday. This comes from a calculated column in my date table using the DAX:
EOW = [CalendarDate]+7-WEEKDAY([CalendarDate]-7) Each column date must display the end of week date, I'm wondering if that might be part of the issue?
- ToddChitt
What is the DAX Measure calculation you are using?
Also, have you considered Visual Calculations? It is ideas for complex things like moving window averages.
- rbowen
I'm using the following measures for total sales and total gross margin (if that's what meant). Thought I was using Calculate(Sum but turns out I'm using SUMX instead (wonder if I should be using calculate(sum instead?):
TotalSales = SUMX('Sales','Sales'[Net Sales])TotalMargin = SUMX('Sales','Sales'[Gross Margin])GrossMargin% = DIVIDE([TotalMargin],[TotalSales])I've done some reading on visual calculations, but not sure they'd work for this particular application. The screen shots I provided in the OP were only for 1 store. There are nearly 70 stores and dozens of department names and numbers and the report users want to maintain the ability to export the BI matrix to Excel. A bar or column chart would be too busy/crowded I think.- ToddChitt
the CALCULATE ( ) wrapper on SUM is only if you need add something like a FILTER statement after it. CALCULATE ( SUM ( 'table'.[field] ) is just the same as SUM ( 'table'.[field] ). I suggest SUM over SUMX.
Your screen shot shows an 8 week average and THAT is the piece that Visual Calculations can help with. Basically you could calculate that value for every week in the matrix.
