Showing SubAvarage As in Excel

[Kray]

Hello friends, 

I want to show subaverage like in excel, it is possible on powerbi?

[Anonymous]

Hi @Kray,

You can use matrix visual to achieve your requirement.

Notice: if your value field stored measure who has specific filters, it not works well on total level, you need to add condition and total level formula to deal with subtotal level calculation.

Reference link:

Clever Hierarchy Handling in DAX

DAX calculations with hierarchies: Set the order straight.

Regards,

Xiaoxin Sheng

[Kray]

sorry I didnt explain enough at first message, mine is already matrix visual. I want to see sum on rows but i want to see the average on row subtotal.

for example , in your 1973 data , i want to see sum for every month but row subtotal has to calculate average. (197301+197302+197303+...)/12   , average of months what I see on powerbi.  Pbi should not calcuate average on back data. 

[Anonymous · Accepted solution]

HI @Kray,

Do you mean only calculate average on total level, right?

If this is a case, current power bi not support auto calculate on summarized value.

Goal: calculate on summarized amount to get average.
Actual: power bi will calculate on underlying data instead of summarized value.

For this scenario, you need to add a condition to filter on total row and use specific formula to calculate on total level.

Sample:

Avg on SubTotal =
VAR temp =
    SUMMARIZE (
        'Sample',
        [Date].[Year],
        'Sample'[Yearmonth],
        "Total", SUM ( 'Sample'[Amount] )
    )
RETURN
    IF (
        COUNTROWS ( 'Sample' )
            = COUNTROWS ( FILTER ( ALL ( 'Sample' ), [Date].[Year]=MAX([Date].[Year]) ) ),
        AVERAGEX ( FILTER ( temp, [Date].[Year]=MAX('Sample'[Date].[Year]) ), [Total] ),
        SUM ( 'Sample'[Amount] )
    )

Regards,

Xiaoxin Sheng

[Kray]

Thank you so much Sheng 🙂