Column Total Averages Not Quite Correct in Matrix

[rbowen]

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?

[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? 

[Shahid12523]

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.

[rbowen]

@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. 

[Ashish_Mathur]

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.

[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.

[rbowen]

@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. 

[ToddChitt]

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.

[rbowen · Accepted solution]

@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!