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.