Forum Discussion

Fusilier's avatar
Fusilier
Icon for Helper III rankHelper III
2 years ago
Solved

Sort 100% stacked bar chart for survey

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?
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    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

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Fusilier
      Icon for Helper III rankHelper III
      2 years ago

      Thank you,

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

      • Ritaf1983's avatar
        Ritaf1983
        Icon for Super User rankSuper User
        2 years ago

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