Percentage in clustered column chart visual

[jgiles]

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

[AllisonKennedy · Accepted solution]

@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. 🙂 

[AllisonKennedy · Accepted solution]

@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. 🙂 

[pipkinp86]

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.

[jgiles]

@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 · Accepted solution]

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

[jgiles]

@AllisonKennedy Thank you so much for you help!  That worked.  Appreciate it 🙂

[jgiles]

Thank you @AllisonKennedy Appreciate the help!

[HotChilli]

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?

[jgiles]

Thank you @HotChilli