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
Aimeeclaird
Helper IV
Helper IV

Compare Row Total and Grant Total in Visual

Hi, I am working with some survey data (apologies, it's sensisitve so cannot share examples)

 

Aimeeclaird_0-1611657248667.png

 

I have this matrix, which shows the row total by category and the grant total on the bottom row. 

 

I want to present this as a visual for the user, I've therefore used a 100% stacked column chart however I am now unable to compare the category % to the grant total. 

 

The idea is that I want to be able to easy see whether a category has a higher or lower % compared to the overal data. 

 

Aimeeclaird_1-1611657336418.png

 

Help appreciated!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Aimeeclaird ,

 

To what I can understand you want to have a total column in the stacked column visualization I made a similar answer today you need to do the folllowing:

 

  • Add a new table with the following syntax:
Axis_values = union(DISTINCT('Table'[AxisCategory]); {"Total"})

Now create the following measure:

Stacked =
SWITCH (
    SELECTEDVALUE ( 'Axis_values'[AxisCategory] );
    "Total";
        CALCULATE (
            SUM ( 'Table'[value] );
            FILTER (
                'Table';
                'Table'[LegendCategory] = SELECTEDVALUE ( 'Table'[LegendCategory] )
            )
        );
    CALCULATE (
        SUM ( 'Table'[value] );
        FILTER (
            'Table';
            'Table'[AxisCategory] = SELECTEDVALUE ( 'Axis_values'[AxisCategory] )
        )
    )
)

 

Result below and in attach PBIX file.

 

 

 


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

1 REPLY 1
MFelix
Super User
Super User

Hi @Aimeeclaird ,

 

To what I can understand you want to have a total column in the stacked column visualization I made a similar answer today you need to do the folllowing:

 

  • Add a new table with the following syntax:
Axis_values = union(DISTINCT('Table'[AxisCategory]); {"Total"})

Now create the following measure:

Stacked =
SWITCH (
    SELECTEDVALUE ( 'Axis_values'[AxisCategory] );
    "Total";
        CALCULATE (
            SUM ( 'Table'[value] );
            FILTER (
                'Table';
                'Table'[LegendCategory] = SELECTEDVALUE ( 'Table'[LegendCategory] )
            )
        );
    CALCULATE (
        SUM ( 'Table'[value] );
        FILTER (
            'Table';
            'Table'[AxisCategory] = SELECTEDVALUE ( 'Axis_values'[AxisCategory] )
        )
    )
)

 

Result below and in attach PBIX file.

 

 

 


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



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.