Clustered Column chart that shows % of row total by x-axis value

[Aimeeclaird]

Hi, I have some text data from a survey. In a matrix I can show the values correctly but I want to visual the same in a chart. 

I've found similar threads but all solutions are based on sum of the value however my data is all text. 

Help please!

[v-xiaoyan-msft · Accepted solution]

Hi @Aimeeclaird,

Based on the information you provided, I made a simple sample, you can try to create a metric like this:

Measure =

var _re=COUNT('Table'[Response])

var _tot=CALCULATE(COUNT('Table'[Response]),ALLEXCEPT('Table','Table'[Job Role]))

return DIVIDE(_re,_tot)

Then put this measure in the matrix and turn it to column chart. Adjust the format according to your needs, then you can get the chart below.

An attachment for your reference

Hope it helps,

Caitlyn Yan

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

[Aimeeclaird]

Hi @v-xiaoyan-msft 

Thank you for this. It works! However I wondered if you could help me. Now that I have the % for the job role on the x-axis, is it possible to make it so if I filter the page with a slicer, the job role still totals 100%?

for example, 

here I have no filters and each job role on the X totals 100% using your measure which is great.

If I then want to filter by Teachers from Primary schools only, I want the totals to still be 100%. (Currently it totals the % of Teachers that are from Primary schools). 

[v-xiaoyan-msft]

Hi @Aimeeclaird,

 I made a simple sample, but it may not match your needs, would you mind providing your sample data without any confidential Information?

Thanks，

Caitlyn Yan

[Aimeeclaird]

Thanks for your support on this @v-xiaoyan-msft 

Unfortunatly the data is confidential however I found an alternative solution (needed to get the report finished!) and used a stacked column chart instead. 

[Aimeeclaird]

I have just tried the following measure, however I am getting 100% for all 

Percentage by response 2 =

DIVIDE (

COUNT('Combined W/out Overall Trust'[ID]),

CALCULATE (

COUNT('Combined W/out Overall Trust'[ID]),

ALLEXCEPT('Combined W/out Overall Trust', 'Combined W/out Overall Trust'[Job Role])))