Forum Discussion
Percentage in clustered column chart visual
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.
Community Championjgiles 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:
'
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.
AllisonKennedy Thank you so much for you help! That worked. Appreciate it 🙂
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.
