Custom sorting a table

[SLK-School]

Hello. A complete novice here I'm afraid. I work in a school and we are just exploring what power bi can do to help us with data analysis.  I am trying to custom sort my tables to accomdate our grading bands (beginning, working towards, expected, etc.) as it only wants to sort alphabetically at the moment.  Can anyone point me in the right direction of how I can set this up please? also if there is a way to assign colours to them all more permanently (for charts and graphs) that would be great i.e beginning - red, working towards - orange. I know how to manually change the colours but wondered if there is a way to automate this. Thanks in advance for any hints and tips! 

[amitchandak · Accepted solution]

@SLK-School ,

Create a column in Power Query named grading bands sort and mark it as the sort column from Column Tools in the visual UI.

If you create it in DAX, you need an additional column to avoid circular dependencies.

grading bands 1 = [grading bands]

Then sort grading bands 1  on grading bands sort and use that in vsiaul

Power query new column

if [GradingBand] = "Beginning" then 1 
      else if [GradingBand] = "Working Towards" then 2 
      else if [GradingBand] = "Expected" then 3
      else 999  // catch-all for anything else

DAX

grading bands sort=
SWITCH(
    [GradingBand],
    "Beginning", 1,
    "Working Towards", 2,
    "Expected", 3,
    999  // default
)

For conditional formatting you can have measure like

GradingBandColor =
SWITCH(
    SELECTEDVALUE( 'GradesTable'[GradingBand] ),
    "Beginning", "#FF0000",          // Red
    "Working Towards", "#FFA500",    // Orange
    "Expected", "#00B050",           // Green, for example
    "#808080"                        // Default gray if not matched
)

And use in conditional formatting using Field value option

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

 How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...