cancel
Showing results for
Did you mean:  Helper III

## how to get Average grand row total

I have matrix chart which shows % of row total.  I just dragged "Sales" field in value and used "Percent of row total".  But I want my grand ROW total to display % Average instead of Sum.  What formula should I use? What formula can I use to get the % of row total and at the same time the grand row total will be average of % row total?  Attached below is the % and actual values. 1 ACCEPTED SOLUTION  Super User III

Hi @summer18 ,

Try the following measure:

``````AverageValue =
VAR Percent_value =
DIVIDE (
SUM ( 'Table'[Value] ),
CALCULATE (
SUM ( 'Table'[Value] ),
ALLEXCEPT ( 'Table', 'Table'[Sub-Category] )
)
)
RETURN
IF (
HASONEVALUE ( 'Table'[Sub-Category] ),
Percent_value,
AVERAGEX (
SUMMARIZE (
'Table',
'Table'[Sub-Category],
"Average_total",
VAR Percent_value_2 =
DIVIDE (
SUM ( 'Table'[Value] ),
CALCULATE (
SUM ( 'Table'[Value] ),
ALLEXCEPT ( 'Table', 'Table'[Sub-Category] )
)
)
RETURN
Percent_value_2
),
[Average_total]
)
)``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português 2 REPLIES 2  Super User III

Hi @summer18 ,

Try the following measure:

``````AverageValue =
VAR Percent_value =
DIVIDE (
SUM ( 'Table'[Value] ),
CALCULATE (
SUM ( 'Table'[Value] ),
ALLEXCEPT ( 'Table', 'Table'[Sub-Category] )
)
)
RETURN
IF (
HASONEVALUE ( 'Table'[Sub-Category] ),
Percent_value,
AVERAGEX (
SUMMARIZE (
'Table',
'Table'[Sub-Category],
"Average_total",
VAR Percent_value_2 =
DIVIDE (
SUM ( 'Table'[Value] ),
CALCULATE (
SUM ( 'Table'[Value] ),
ALLEXCEPT ( 'Table', 'Table'[Sub-Category] )
)
)
RETURN
Percent_value_2
),
[Average_total]
)
)``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português   Helper III

Thank you very much @MFelix .  It works! Announcements #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps Top Solution Authors
Top Kudoed Authors
Users online (2,500)