Forum Discussion

rbowen's avatar
rbowen
Helper III
1 year ago
Solved

Column 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 ...
Need Help
  • rbowen's avatar
    rbowen
    1 year ago

    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!

pankajnamekar25's avatar
pankajnamekar25
Super User
1 year ago

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's avatar
    rbowen
    Helper III
    1 year ago

    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?