Forum Discussion
Percentage in clustered column chart visual
Hi,
I have a clustered column chart visual and the percentage is calculating across the grand total. I'm trying to create a measure or column to calculate the level % by year. Example Level A is 25%, B, 25%, C 40% and D 10% for YEAR 2020. I also provided a table below of what I'd want the percentage to be on the graph. Also, a sample table of the data is below.
| Correct Percentage to display | |||
| Level | 2020 | 2021 | 2022 |
| A | 12.08% | 41.33% | 18.75% |
| B | 66.44% | 48.44% | 0.00% |
| C | 10.74% | 10.22% | 31.25% |
| D | 10.74% | 0.00% | 50.00% |
| 100.00% | 100.00% | 100.00% |
Sample table:
| YEAR | Level | Count |
| 2021 | B | 109 |
| 2020 | B | 99 |
| 2021 | A | 93 |
| 2021 | C | 23 |
| 2020 | A | 18 |
| 2020 | C | 16 |
| 2020 | D | 16 |
| 2022 | D | 8 |
| 2022 | C | 5 |
| 2022 | A | 3 |
Thank you,
Julie
jgiles I started working on this before HotChilli replied and then got interrupted. Am coming back to it now, so to expand on what HotChilli said, here's exactly how you can clear the filter on Level.
You've selected the % of GT - which clears filters on Level AND Year. You only want a subtotal, so only want to clear the filter on Level. Using the ALL function in DAX in combo with CALCULATE we can do this:
% of all Levels Count =DIVIDE(SUM(LevelsSample[Count]), CALCULATE(SUM(LevelsSample[Count]), ALL(LevelsSample[Level])))'
Sample file attached below signature. Thanks jgiles for the easy to copy/paste sample data - you made it very easy to answer your question. 🙂
jgiles That's an interesting one I haven't noticed, but it makes sense if my guess is right: My guess is that the column level is sorting by is actually applying the filter too, so you need to add
ALL(Table[LevelSortColumn])
as a filter argument too and that should do the trick.
8 Replies
- HotChilli
Community Champion
The value on the chart is a measure that represents SUM(SampleTable[Count]),
so you need to write a measure that takes this value and divides it by the same measure which has the Level filters removed.
Is that enough to get you started?
- AllisonKennedy
jgiles I started working on this before HotChilli replied and then got interrupted. Am coming back to it now, so to expand on what HotChilli said, here's exactly how you can clear the filter on Level.
You've selected the % of GT - which clears filters on Level AND Year. You only want a subtotal, so only want to clear the filter on Level. Using the ALL function in DAX in combo with CALCULATE we can do this:
% of all Levels Count =DIVIDE(SUM(LevelsSample[Count]), CALCULATE(SUM(LevelsSample[Count]), ALL(LevelsSample[Level])))'
Sample file attached below signature. Thanks jgiles for the easy to copy/paste sample data - you made it very easy to answer your question. 🙂
Thank you AllisonKennedy Appreciate the help!
AllisonKennedy One more question. Why wouldn't this work if the Level was sorted by a different column? I'm getting 100% but if I change the sort order of Level back to just Level it works. Thank you!
- AllisonKennedy
jgiles That's an interesting one I haven't noticed, but it makes sense if my guess is right: My guess is that the column level is sorting by is actually applying the filter too, so you need to add
ALL(Table[LevelSortColumn])
as a filter argument too and that should do the trick.
Thank you very much, I wish I had seen this before, I used to break my data in separated tables to get the same result.
