Show total in stacked bar chart like how Power BI automatically calculates total for matrix table

[Anonymous]

I inserted the row, column and value into a Matrix table. It automatically calculates the total for the matrix table. I duplicated the matrix and converted it into a 100% Stacked Bar chart. Question: How can I make it that it shows the total (as shown in the image)? 

Thank you

[v-henryk-mstf]

Hi @Anonymous ,

Since you only provided screenshots, I cannot know your specific data model. So I only tried to build a similar model, according to my test, the total value of the matrix cannot be displayed in a 100% stacked bar chart.

In addition, i would like to ask, what is the calculation logic of the total you want to add, and what value it wants to display?

If there is a problem with the built data model, can you provide a pbix file and point out specific errors, so that I can do further testing. Let me know immediately, looking forward to your reply.

Here is the sample pbix file.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Anonymous ,

What is the exact purpose you want to achieve the image is not very clear about what you want, do you want to have an additional level that gets the 100% for all the categories together?

If that is the purpose you need to do the following:

• 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.

[Anonymous]

Hi @MFelix 

Here's my reply... let me edit a few times because of invalid HTML error first... I think I can't create a table else it will give me the HTML error..

Apologies for not making it clearer and proof-reading it before posting it out, but you nailed my thoughts. I want it to be like that! I've read your DAX codes and tried to reapply it to my tables but it didn't work out.

For my case, the formula you have provided in Line 6, my Table value is not a Whole Number type, but rather it is a Text type where it performs a COUNTIF equivalent to get the count of each entry, something like the below table, where I COUNTIF by work location and age group...

Work Location | Age Group

Australia | 30 < 40

Australia | 30 < 40

Malaysia | 30 < 40

Malaysia | 50 < 60

Malaysia | 30 < 40

to get the matrix of

Work Location | 30 < 40 | 50 < 60

Australia | 2 | 0

Malaysia | 2 | 1

Apologies for any inconvenience caused and thank you. Will be trying to digest and trying my best to understand more about DAX code and solving this on my end in the meantime....

[MFelix · Accepted solution]

Hi @Anonymous ,

You need to redo the sum to a count. I have used you example and prepared the following measures:

Table

Axis_values = union(DISTINCT(Age_Group[Work Location]); {"Total"})

Measure:

Stacked = 
SWITCH (
    SELECTEDVALUE ( 'Axis_values'[Work Location] );
    "Total";
        CALCULATE (
            COUNT(Age_Group[Age Group]);
            FILTER (
                Age_Group;
                'Age_Group'[Age Group] = SELECTEDVALUE (Age_Group[Age Group] )
            )
        );
    CALCULATE (
        COUNT(Age_Group[Age Group]);
        FILTER (
            Age_Group;
            Age_Group[Work Location]= SELECTEDVALUE ( 'Axis_values'[Work Location] )
        )
    )
)

Result: