Compare Row Total and Grant Total in Visual

[Aimeeclaird]

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

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. 

Help appreciated!

[MFelix · Accepted solution]

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.