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
summer18
Helper III
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?

 

 

% of row total.jpg

 

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.

 

img003.jpg

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you very much @MFelix .  It works!

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.