Sort Legend Dynamically By its Value

[schoden]

Hi Community, 

Is it possible to sort the legend dynamically by the value in its column( highest to lowest)  

There is hardcoded way which is not I am after or alphabetically sorting.

Sort each bar with highest value as first stacked bar, least value on top.

Just like the Feb Month highest value first (Black color), Red color the least value on top of the bar.

 

[TheoC]

@schoden figured out how to do it. There is very limited stuff out and about but anyway, hopefully the below screenshot is kind of what you were wanting?

You will need to create the following measures and two tables:

S_Table2_Cat uses Summarised. 

S_Table3_Status is just "Enter Data" and column title "Status" with Top, Middle, Bottom as data.

Relationships in Model view as follows:

Hope it's what you're after mate! Was interesting to say the least lol!

[TheoC]

Hi @schoden, if you assign a number to each color (i.e. 1, 2, 3), you can then "Sort by Column" under the "Column Tools" ribbon.  I have applied something similar to the Covid Dashboard (https://www.axiar.com.au/covid19-australia) in terms of Close, Low Risk and Casual Contact exposures.

Best way to do this is by going to "Transform Data" and creating a Conditional Column.  If "color" equal "Black" then 1, Blue then 2, Red then 3, and so on. Close and apply. Click the "Color" field in the right Fields pane, then go to "Sort by Column" and sort by the new column. Finito 🙂

Let me know if you'd like more detailed steps 🙂

[TheoC]

@schoden if you want to use dax to achieve the above, you can create a summarised table, add a Calculated Column using SWITCH to change the color name to a digit, and link the summarised Color field to original table. Then create a Calculated Column using "Related" in the original table to bring back the digits.

1. Color Table = SUMMARIZE ( Table1 , [Color]) 
2. Color Rank = SWITCH ( [Color] , "Black" , 1 , "Blue" , 2 , "Red" , 3 , 0 )
3. Relationship Color Table [Color] to Table1 [Color] (one is to many)
4. In Table1 create Calculated Coloumn
   1. rColor = RELATED ( Color Table[Color] )

Done 🙂

[schoden]

Hi @TheoC  This will always put Black color as 1, Blue as  2, Red as 3 as its hardcoded, 

I want dynamically sorting based on values  alloted to the colors. 

Feb  ( Black= 7, Blue=6, Red= 5 )  which is stacked as Black, Blue , Red .

March ( Blue= 8, Red=8, Black= 1) which is stacked as Blue , Red , Black.

[TheoC]

@schoden ,I found this. Using the logic behind it, if the number of colors is fixed (I.e. 3 each month), you could create unique measures to total the count of a category by rank, then allocate ta colour to the rank... the rank (I.e. 1, 2 or 3) is used to sort the chart (apologies for typos, on phone) https://community.powerbi.com/t5/Desktop/How-do-I-color-the-stacked-bar-chart-by-Dax-query-according...

[schoden]

Hi @TheoC  Thanks for the getting back to me. The solution provided will not work for me as everything is dynamic and number to the color is will varry and change.  

Thanks for your response though. appreciate it. 

[TheoC]

@schoden I just finished adding a new comment with screenshots and all that fun stuff. Hopefully achieves what you want! Link to PBIX below!

PBIX File 

[TheoC · Accepted solution]

@schoden did you check out the solution I put forward? I attached the PBIX in an earlier reply. I believe it is what you were after. Hope it helps!