Using SUMX with other functions

[Nanakwame]

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 

[amitchandak · Accepted solution]

@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 · Accepted solution]

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

[amitchandak · Accepted solution]

@Nanakwame ,

On top this

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

Create a new measure

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