topic Re: Percent of grand total showing incorrectly in column chart with small multiples. in DAX Commands and Tips

Percent of grand total showing incorrectly in column chart with small multiples.

sunilbeckham — Thu, 31 Aug 2023 20:23:42 GMT

Hello! I'm trying to show the percentage calculation in a clustered column chart with small multiples of two. However, I can't seem to get the denominator value right in my visual. My table looks something like this:

Student Number	Gender	Attendance
100	Male	Very Good
101	Female	Very Good
102	Female	Inconsistent
103	Male	Poor
104	Male	Good

Currently, I'm plotting this information in a chart with small multiples of Gender column, X axis of Attendance column, and Y axis being a simple COUNTA(Student Number) column that I show as Percent of grand total. Here's the screenshot of how it looks presently:

Expected result is that each gender field should have a combined total of 100% across the 4 attendance categories. For example, in this visual it should display something like this:
Female- Very Good = 40%, Good = 30%, Inconsistent = 20%, Poor = 10%
Male- Very Good = 30%, Good = 30%, Inconsistent = 20%, Poor = 20%

Thanks in advance!

Re: Percent of grand total showing incorrectly in column chart with small multiples.

Anonymous — Mon, 04 Sep 2023 02:13:58 GMT

Hi sunilbeckham ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure.

Measure = var _a=COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Gender]=MAX('Table'[Gender]))) var _b=COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Gender]=MAX('Table'[Gender]) && 'Table'[Attendance]=MAX('Table'[Attendance]))) return DIVIDE(_b,_a)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

Re: Percent of grand total showing incorrectly in column chart with small multiples.

sunilbeckham — Mon, 11 Sep 2023 17:37:23 GMT

Thank you so much for your response and providing the PBIX file. I tried a similar approach to yours and it worked for me. I ended up writing this measure:

DIVIDE(COUNTA(Student Number), CALCULATE(COUNTA(Student Number), ALLSELECTED(Table), VALUES(Table[Gender]) ))

I appreciate your input and help regarding this. Thank You!