topic Re: Change Grand total sum to avg in power bi in DAX Commands and Tips

Change Grand total sum to avg in power bi

Anonymous — Thu, 09 Jul 2020 11:04:24 GMT

Hi All,

below data i have grand total row. where it is actually summing up the values in total section.

but i want to display average there, for example in this case it should display 87 not 439.

i have tried many measure ideas, below is one.

kindly help

Sum - Avg =

VAR avgperf = sum(SumView[Category Pref.])

Return

IF(

HASONEVALUE('Calender Date'[Month])

,avgperf

,AVERAGEX(SumView,avgperf))

AntrikshSharma — Thu, 09 Jul 2020 12:56:59 GMT

Try

Sum - Avg = AVERAGEX ( VALUES ( 'Calender Date'[Month] ), CALCULATE ( SUM ( SumView[Category Pref.] ) ) )

Anonymous — Thu, 09 Jul 2020 13:04:08 GMT

HI AntrikshSharma thanks for your time in answering my question.

I actually tried many measures from the morning nothing worked out.

Strangley below measure working as per my expectation.

Mavg = IF

(HASONEVALUE(SumView[Category]),

[Measure 2],

AVERAGEX(VALUES(SumView[Category]),[Measure 2])

)

Measure2 means : sum(categoryPref)