Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kray
Helper I
Helper I

Showing SubAvarage As in Excel

Hello friends, 

 

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

 

subavarage.JPG

1 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] )
    )

4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @Kray,

 

You can use matrix visual to achieve your requirement.

2.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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. 

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] )
    )

4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you so much Sheng 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.