Sort 100% stacked bar chart for survey

[Fusilier]

Hi, hope somebody can help?

I have some survey data I'm trying to analyse.

Response to a question is either:

Fairly safe

Fairly unsafe

Neither safe nor unsafe

Very safe

Very unsafe

And the responses are grouped by age (55-64, 65+ etc.) of the respondent.

I've created this 100% stacked bar chart :

I'm trying to sort the chart by 'the Fairly safe %' descending.

I've searched online and followed a suggestion of creating  a measure and adding it as a tooltip and then sorting the axis by the tooltip:

Count Answer Fairly safe =

CALCULATE(

    COUNTROWS('All Responses (2)'),

    'All Responses (2)'[Value]="Fairly safe"

)

The chart is sorting by the count of the value "Fairy safe", but I want it to sort by the % of the value not the count. So I want to see from top to bottom: 38% followed by 34% then 31% and so on.

Is there a way of changing the measure to calculate the % and not the count?

[Ritaf1983 · Accepted solution]

Hi @Fusilier 
If you need to sort the stacked graph by its "slices"  you need to create separate measures for every group at the legend like :

Fairly safe = CALCULATE(COUNTROWS('Table'),'Table'[answer]="Fairly safe")

Fairly unsafe = CALCULATE(COUNTROWS('Table'),'Table'[answer]="Fairly unsafe")

Neither safe nor unsafe = CALCULATE(COUNTROWS('Table'),'Table'[answer]="Neither safe nor unsafe")

Then you can select by which group you want to sort the graph :

The pbix with the example is attached
P.s It's important to note that stacked graphs are complex to read and not really effective for data visualization. I'm attaching a link to my article on the subject, including an alternative.

https://www.madeiradata.com/post/home-experiments-and-stacked-bar-graphs

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

[Fusilier]

Thank you,

I read your article as well and will try your alternative method as well.

[Ritaf1983]

Thank you very much, nothing makes me happier than a complicated graph being discarded in favor of effective visualization 🙂😍