Forum Discussion
Sort Legend Dynamically By its Value
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.
- Color Table = SUMMARIZE ( Table1 , [Color])
- Color Rank = SWITCH ( [Color] , "Black" , 1 , "Blue" , 2 , "Red" , 3 , 0 )
- Relationship Color Table [Color] to Table1 [Color] (one is to many)
- In Table1 create Calculated Coloumn
- rColor = RELATED ( Color Table[Color] )
Done 🙂
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.
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-to/td-p/1018392
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.