Clustered Column Chart: Display Percentage of Legend

[m_richardson]

The chart below displays the count of survey responses for each response type (e.g. Agree, etc) by year.

Because there are a different number of respondees per year I'd like to convert this to percentages, so that each bar is a proportion of the overall year and therefore more easily compared. If I select 'Show value as' I only get the option to display as a Percent of the grand total, so I know I need to create a measure here for this to work properly.

Please note I also have a filter on the chart for a column called 'Question' that pinpoints the specific question asked.

What should the measure look like so that each the reponses total to 100% for the year, and so that I can more directly compare the bars as percentages rather than values?

[v-zhangti · Accepted solution]

Hi, @m_richardson 

You can try the following methods.
Sample data:

Measure = 
Var _N1=CALCULATE(COUNT('Table'[Question]),ALLEXCEPT('Table','Table'[Response],'Table'[Year]))
Var _N2=CALCULATE(COUNT('Table'[Question]),ALLEXCEPT('Table','Table'[Year]))
Return
DIVIDE(_N1,_N2)

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

@m_richardson , for custom label check

Custom label
https://www.youtube.com/watch?v=IH4bcHhxunw
Dynamic Label
https://www.esbrina-ba.com/time-intelligence-dynamic-legend-in-line-charts/

For Sub Total/Total  refer

Percent of Total and Percent of SubTotal https://www.youtube.com/watch?v=6jTildcV2ho
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=24270s