Forum Discussion

andhiii102030's avatar
andhiii102030
Frequent Visitor
2 years ago
Solved

Sort by column within sub groups

Hi,   i have a special problem and no good solution right now for this case. I have KPI which are group by categories.  Costs: is, calculated, should profit: is, calculated  No i try to so...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi andhiii102030 ,

    What problem do you encounter here? I think concatenating strings is a valid solution.

    Some tricks like to add space after "is" is not a solution to create unique values in the table: "is " or "is  "

    Here are my steps:

    1.Creating an index column after grouping in Power Query(The index column is not used for the final sort)

    2.Use the following DAX expression to create  columns

    Column = [Count.Sort column] & REPT(" ",[Index])
    Column 2 = SWITCH(
    TRUE(),
    [Count.KPI] = "costs" && [Count.Sort column] = "is",1,
    [Count.KPI] = "costs" && [Count.Sort column] = "calculated",2,
    [Count.KPI] = "costs" && [Count.Sort column] = "should",3,
    [Count.KPI] = "profit" && [Count.Sort column] = "is",2,
    [Count.KPI] = "profit" && [Count.Sort column] = "calculated",1
)

    3.Final output

     

    Best Regards,
    Wenbin Zhou

     

suparnababu8's avatar
suparnababu8
Icon for Super User rankSuper User
2 years ago

Hi andhiii102030 

Here’s a solution using DAX that should help you achieve the desired sorting without creating additional tables.

  1. Create a Calculated Column for Sorting: You can create a calculated column in your ‘fact’ table that assigns a sort order based on the KPI group and KPI name. Use the SWITCH function to handle different sorting orders for different groups.

 

SortOrder = 
SWITCH(
    TRUE(),
    [KPI-group] = "Costs" && [KPI] = "is", 1,
    [KPI-group] = "Costs" && [KPI] = "calculated", 2,
    [KPI-group] = "Costs" && [KPI] = "should", 3,
    [KPI-group] = "Profit" && [KPI] = "is", 2,
    [KPI-group] = "Profit" && [KPI] = "calculated", 1,
    -- Add more conditions as needed for other groups
    BLANK()
)

 

Sort by the Calculated Column: Once you have the SortOrder column, you can use it to sort your KPIs in the visual.

  • Go to the ‘fact’ table in Power BI.
  • Select the KPI column.
  • In the ribbon, click on Sort by Column and choose the SortOrder column.
  • This approach ensures that each KPI is sorted according to the specific order defined for its group.