Forum Discussion

Nanakwame's avatar
Nanakwame
Helper II
4 years ago
Solved

Using SUMX with other functions

Hi Experts,

 

I currently have a dax function 

Margin % Today = CALCULATE(SUM(dailyKPI[Margin %]),'Date'[Date] = TODAY() -1) to calculate margin % for yesterday. This is being used in a table view so it sums up the total. please see the image attached. Instead of 315.4%, i will like for it to display the average of all the margin%. I will like for it not to sum the total but instead take the average and use it as total.
Also is there a way to exclude weekends when it is calculating yesterday's margin? for instance it will calculate friday's margin on monday instead of calculating sundays margin. 
 
Thanks in advance 
 

 

Data Protection
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    Nanakwame ,

    On top this

    Margin % Today = CALCULATE(SUM(dailyKPI[Margin %]),'Date'[Date] = TODAY() -1)

     

    Create a new measure

    averageX(values(dailyKPI[Warehouse]) , [Margin % Today])

  • AllisonKennedy's avatar
    AllisonKennedy
    4 years ago

    Nanakwame  I agree with amitchandak  on how to get the average calculation. See my post here if you want to understand more why: https://excelwithallison.blogspot.com/2020/09/what-does-average-mean.html 

     

    As for ignoring weekends, you can do this easily if you have a dimDate table that has a column that flags weekends or at very least a weekday column. 

    https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

     

     It looks like you have a date table, so just depends on how you create the weekday column.

     

    Assuming a 'Date'[Weekday] column that flags weekday as 1 and weekends as 0, the new measure would look something like: 

     

    Margin % Today = 

    VAR _Today = TODAY()

    VAR _Yesterday = MAXX( FILTER( 'Date', 'Date'[Date] < _Today && 'Date'[Weekday] = 1 ), 'Date'[Date] )

    RETURN

    CALCULATE(SUM(dailyKPI[Margin %]),'Date'[Date] = _Yesterday)

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Nanakwame ,

    On top this

    Margin % Today = CALCULATE(SUM(dailyKPI[Margin %]),'Date'[Date] = TODAY() -1)

     

    Create a new measure

    averageX(values(dailyKPI[Warehouse]) , [Margin % Today])

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    4 years ago

    Nanakwame  I agree with amitchandak  on how to get the average calculation. See my post here if you want to understand more why: https://excelwithallison.blogspot.com/2020/09/what-does-average-mean.html 

     

    As for ignoring weekends, you can do this easily if you have a dimDate table that has a column that flags weekends or at very least a weekday column. 

    https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

     

     It looks like you have a date table, so just depends on how you create the weekday column.

     

    Assuming a 'Date'[Weekday] column that flags weekday as 1 and weekends as 0, the new measure would look something like: 

     

    Margin % Today = 

    VAR _Today = TODAY()

    VAR _Yesterday = MAXX( FILTER( 'Date', 'Date'[Date] < _Today && 'Date'[Weekday] = 1 ), 'Date'[Date] )

    RETURN

    CALCULATE(SUM(dailyKPI[Margin %]),'Date'[Date] = _Yesterday)