Line and stacked column chart - Sort column legend

[Anonymous]

Hi thank you for reading!

I'm working on a survey data with a 6-point satisfaction rating scale: Very Dissatisfied, Dissatisfied, Slightly Dissatisfied, Slightly Satisfied, Satisfied, and Very Satisfied. Within this dataset, there are two years worth of survey data, and I need to visualise my data in 100% stacked column chart, together with the mean score. Please find below the fields used for better understanding:

Shared axis: Year

Column series: Question

Column values: Measure with the following DAX formula

Measure =

VAR Val2AllCats = CALCULATE(COUNTA('All Data'[Question]),ALL('All Data'[Question]))

RETURN

DIVIDE(COUNTA('All Data'[Question]),Val2AllCats)

Line values: Average of Question

However, the problem I faced was that I'm not able to sort the legend of the column chart in the order of the satisfaction 6-point rating scale. With all the fields above, I've managed to come up with the chart I wanted. However, the satisfaction levels are arranged in alphabetical order. 

I have tried to sort the order of the column chart using relationships and a table to define each satisfaction level and an index column. However, once I sort the satisfaction label by the index, and replaced Column series with "Satisfaction Level", the chart shown changes and I get the following outcome:

[amitchandak · Accepted solution]

@Anonymous , what is the legend you have taken.

It need to all for rating

VAR Val2AllCats =
RETURN
DIVIDE(COUNT('All Data'[Question]),CALCULATE(COUNT('All Data'[Question]),ALL('All Data'[Rating])))

or

VAR Val2AllCats =
RETURN
DIVIDE(COUNT('All Data'[Question]),CALCULATE(COUNT('All Data'[Question]),ALL('All Data'[Question])))

refer:https://www.youtube.com/watch?v=aXpgUM-dSmI

https://www.tutorialgateway.org/create-100-stacked-bar-chart-in-power-bi/