Sort by column within sub groups

[andhiii102030]

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 sort this KPI in their own sub group:

The problem ist, that the KPI "is, calculated and should" have their order 1,2,3 

But it can happened that in a other group is and calculated have a opposite order 2,1 (see red box)

I try to solve it to create a own column for this sorting and to have the KPI-GROUP-KPI column for unique values:

and create a relationsship via KPI-group-KPI

But how it is possible to sort a column in one case after one column and in another case after a other column.
Some tricks like to add space after "is" is not a solution to create unique values in the table: "is " or "is  "
I want to avoid additional tables. 
Did someone have a smart solution? 

Thank you!

[v-zhouwen-msft · Accepted solution]

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]

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.

[andhiii102030]

But, this do not work: Because "is" has value 1 and 2. And this is right, because of the different sorting in the categories.

[suparnababu8]

Hi @andhiii102030 

It looks like you’re trying to create a custom sorting order for your KPIs using the SWITCH function in DAX, but you’re encountering an issue because the value “is” appears in both the “Costs” and “Profit” groups with different sort orders.

To resolve this, you can use a nested SWITCH function or a combination of IF statements to ensure that each condition is unique. Here’s an updated version of your measure:

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", 4,  -- Changed to 4 to avoid conflict
    [KPI-group] = "Profit" && [KPI] = "calculated", 5,  -- Changed to 5 to avoid conflict
    -- Add more conditions as needed for other groups
    BLANK()
)

By assigning unique values to each condition, you can avoid conflicts and ensure that your sorting works correctly. If you have more conditions to add, just make sure each combination of [KPI-group] and [KPI] has a unique sort order value.

[andhiii102030]

You can not sort the KPI column if in the sort column are different values for "is".
For my seems that i talk to a KI bot....