itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: sort values according to my needs

To sort, I have the 2 default options called “sort ascending” and “sort descending” but they may not sort like how I want so I will need to customize it. For example, I have this table:

power bi

I create a slicer and by default, it sorts them ascending but the result is not what I want because what I want, it is to sort it like in my table:

power bi

Even if I do it by descending, I don’t get what I want:

power bi

Let’s start by the easy one, to sort the name column, I will create an index column:

power bi

NOTE: to know how, read Power BI: sort correctly with index/ranking

Then configure the name column to use this index column to sort them with the “sort by column” option:

power bi

For the “group” column, I will extract it by creating a new table and duplicating it with another name called “sortgroup”:

power bi

NOTE: to know how, read Power BI: extract columns from a table to another one

Then I will create a new column with this simple formula to create an index using the duplicate (sortgroup) column:

power bi

NOTE: alternatively, I can use the SWITCH function

power bi

And like the name column, I will select the group column and using the “sort by column” option:

power bi

To finalize, I will create a relationship by making sure that "both" is selected in "cross-filter direction":

power bi
power bi

Now, if I recreate my filter using the group from my new table, I get the same sorting as in my table:

power bi

For a better management, particularly for a big data, I will recommend to use excel to manage the index.

Interesting Topics