Forum Discussion
Helper IShowing SubAvarage As in Excel
Hello friends,
I want to show subaverage like in excel, it is possible on powerbi?
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
4 Replies
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.
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
