How to get grand total from averages to provide sum not average?

[VinhTon]

How do I get the Sales by Workdays grand total to give the sum of averages: 521...+ 269...+538...?

Using the powerbi sample dataset I created an avg of sales / num of workdays which gives expected results by month.

For the grand total it logically provides the sales / by total of workdays by 3 months

The formula for Sales by Workdays (avg) = DIVIDE( SUM(financials[Sales]), [Num Of Workdays] )

I tried following this youtube but obviously am not understanding it properly in my sales by workdays (sum of avg) implementation is given totally wrong numbers. Sales by Workdays (sum of avg) =

var avgSales = DIVIDE( SUM(financials[Sales]), [Num Of Workdays] )

Return

IF( HASONEVALUE( 'Date'[Date] ),

avgSales, SUMX( VALUES('Date'[Date] ), avgSales )

)

[DataInsights · Accepted solution]

@VinhTon,

Try rewriting the measure as follows:

Sales by Workdays (sum of avg) =
IF (
    HASONEVALUE ( 'Date'[Month Year] ),
    [Sales by Workdays (avg)],
    SUMX ( VALUES ( 'Date'[Month Year] ), [Sales by Workdays (avg)] )
)

Three things to note:

1. The HASONEVALUE function refers to 'Date'[Month Year]. The column in HASONEVALUE should match the grain of the visual.

2. The VALUES function refers to 'Date'[Month Year]. Since you want to sum the monthly averages, you need to iterate at the month grain.

3. Use the measure name itself instead of a variable. This will cause SUMX to work correctly (context transition).

[VinhTon]

Beautiful, thanks!