Totals do not add to 100% in clustered bar chart

[Anonymous]

Hi Power BI Experts,

For readability, i am showing two out of six bar charts in the graph below. The issue is that the bars do not add up to 100% in its respective category. For example, Dashboards NOW equals ~16%.

I am using this DAX code to calculate the percentage.

Before and After % of Row Total = COUNT('Before and After'[Progress Rating])/CALCULATE(count('Before and After'[Progress Rating]), ALL('Before and After'))

I would like for each bar chart to look like this.

If I add more than one category, the percent is the grand total of both graphs and that is not what I want.

Any help is greatly appreciated.

Luis

[Anonymous]

Success!

Thank you.

Which book or course did you get your DAX skills?

I would like to upskill my DAX proficiency.

Thank you for all of your guidance.

[AlexisOlson]

I've acquired my DAX skills through years of answering questions like yours and reading articles on sqlbi.com and radacad.com. Both of those sites have training videos too and I have done some of the SQLBI Courses.

[AlexisOlson]

Using ALL, removes all the filter context on the table but you only want to remove part of it.

Try just removing the filter context for the Legend field:

Before and After % of Row Total =
COUNT ( 'Before and After'[Progress Rating] )
    / CALCULATE (
        COUNT ( 'Before and After'[Progress Rating] ),
        ALL ( 'Before and After'[Progress Rating] )
    )

[Anonymous]

Thank you Alexis,

I am getting closer. Using the DAX, the bars stretch out to 100% by rating category. However, I would like a percentage for each rating category. All the bars for the respective category would sum up to 100% like the built-in function. 

Would you mind providing another friendly nudge?

[AlexisOlson · Accepted solution]

Hmm. That seems strange to me but it's often hard to debug just by looking at screenshots.

Here's a variation that might be slightly more robust:

Before and After % of Row Total =
COUNT ( 'Before and After'[Progress Rating] )
    / CALCULATE (
        COUNT ( 'Before and After'[Progress Rating] ),
        ALL ( 'Before and After' ),
        VALUES ( 'Before and After'[Progress Category] )
    )

This removes all filters on that table then restores the category filter. Try ALLSELECTED instead of ALL if you want to want to preserve filters from outside the visual other than category.