Sum of a Calculated Average Column

[cgallegos]

Hello,

I am trying to get the subtotal in the December Average Column below to display the total sum of those averages. Instead it is giving me the average. Would someone be able to help me find a solution to get the Sum?

Here is the Average DAX I used. 

Let me know if there is anything else I can provide that would be helpful. Thank you!

[v-zhangti · Accepted solution]

Hi, @cgallegos 

If you are trying to control the output of total, you can use IF(HASONEVALUE(),average output, total output).

For example, the first output in the IF function is the average of the columns in the Matrix view, and the second output is Total, which outputs the sum of the averages.

Measure = 
IF (
    HASONEVALUE ( 'Table'[date] ),
    CALCULATE (
        AVERAGE ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), [date] = MAX ( 'Table'[date] ) )
    ),
    SUMX (
        SUMMARIZE (
            'Table',
            'Table'[date],
            "average",
                CALCULATE (
                    AVERAGE ( 'Table'[sales] ),
                    FILTER ( ALL ( 'Table' ), [date] = MAX ( 'Table'[date] ) )
                )
        ), [average] ) )

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more.

[ValtteriN]

Hi,

You can change the calculation logic in total row using this pattern: IF(ISBLANK(SELECTEDVALUE(Table[Region/Group])),[SUM logic],[Averagex logic])

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[cgallegos]

Hi,

Can you provide a little more guideance? I'm not understanding what to use for the sum logic.

[amitchandak]

@cgallegos , Other than the last solution provided

You can also try Sumx(Values(Table[Region/Group]), [Average Measure])