Total Sum of Grouped Average

[dhestarwirawan]

Hi everyone,

I have the data with a structure like this

I want to create summary like below table (let's call this summary table😞

Where the GOE equals to Opex divided by Sum of Grouped Average TWPI.

For the Sum of Grouped Average TWPI, I wrote the DAX as below (I average the TWPI based on the Business column, and then sum all the grouped average):

Sum of Grouped TWPI = SUMX(SUMMARIZE(

    'Sheet1','Sheet1'[Business],"Total TWPI" ,AVERAGE('Sheet1'[TWPI])),[Total TWPI]

)

The above DAX is works for per company results, but does not work for total. In the summary table above, the total should be 21,828,883.

How to get the correct total sum amount for the TWPI?

Thanks!

[samratpbi · Accepted solution]

Hi, first of all, I created a small subset of your table like below:

Then for for average TWPI, created below measure:

AvgTWPI =

IF(ISFILTERED(SumAvg[Period]),

    AVERAGE(SumAvg[TWPI]),

    SUMX(

        ADDCOLUMNS(

            SUMMARIZE(

                SumAvg,

                SumAvg[Period],

                SumAvg[Company]),

            "Avg_TWPI",

            AVERAGE(SumAvg[TWPI])

            ),

        [Avg_TWPI])

)

and the output is below:

It is taking sum of average fields.

If this helps to resolve your problem then please mark it as solution provided, thanks!