Which is the 'Right' way to operate on a summary table. Average of Averages

[GryphonKD]

Hi Dax gurus,

I have previously used Dax measures to calculate operands on summaries.  Could be Average of Sums, or Sums of averages etc..

I'm currently doing an Average of Averages - except there are some usual results.

Normally I put it all in one measure, and perform an AverageX on a Summarization.

No doubt there are countless posts on this type of problem; here is another, doing the same thing in a slightly differently written measure:

https://community.fabric.microsoft.com/t5/Desktop/How-to-perform-an-average-of-an-average/td-p/16726...

here's my measure:

AVAV-CP-measure =

AVERAGEX(

    SUMMARIZE(WW_Hourly, HouseInfo_Table[Heating & Cooling], HouseInfo_Table[Classfication], "AV-Cooling",

    CALCULATE(

            AVERAGE('WW_Hourly'[Cooling_power]),

            FILTER(WW_Hourly,WW_Hourly[Cooling_power]>0)

    )

),[AV-Cooling]

)

I thought I'd compare this, by generating a summary table first, using the same DAX:

Test_Summary_Table =

SUMMARIZE(WW_Hourly, 'HouseInfo_Table'[Heating & Cooling], 'HouseInfo_Table'[Classfication] , "AV-Cooling",

    CALCULATE(AVERAGE('WW_Hourly'[Cooling_power]),

    FILTER(WW_Hourly,WW_Hourly[Cooling_power]>0)

    )

)

Then get the results through a basic Average measure on that Summary table:

AVAV_CP_Table = AVERAGE('Test_Summary_Table'[AV-Cooling])

here are the results, from the two approaches:

The results from the summary tables are a little different, and so the final results, in the context of classification are a little different:
 

Does anyone know why this could be, and which is the right way? I might have to apply this to simpler dataset to check which method got the summary table correct.

[v-shex-msft]

Hi @GryphonKD ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[anmolmalviya05]

Hi @GryphonKD , Please try the below measure:

Test_Summary_Table =
SUMMARIZE(
WW_Hourly,
HouseInfo_Table[Heating & Cooling],
HouseInfo_Table[Classfication],
"AV-Cooling",
CALCULATE(
AVERAGE(WW_Hourly[Cooling_power]),
FILTER(ALL(WW_Hourly), WW_Hourly[Cooling_power] > 0)
)
)

[danextian]

Hi @GryphonKD 

Don't use SUMMARIZE to add calculated columns. 

ADDCOLUMNS (
    SUMMARIZE (
        WW_Hourly,
        'HouseInfo_Table'[Heating & Cooling],
        'HouseInfo_Table'[Classfication]
    ),
    "AV-Cooling",
        CALCULATE (
            AVERAGE ( 'WW_Hourly'[Cooling_power] ),
            FILTER ( WW_Hourly, WW_Hourly[Cooling_power] > 0 )
        )
)