Number range ordering

[cheid_4838]

I have the below table where the percentages (4%, 5%, 10%, 15%, etc) were created by a switch statement.  I then grouped those percentages into ranges as shown below.  To get the ranges in the correct order I tried another switch statement and another grouping, but both produced an error.  How do I go about putting these ranges in the correct order?  Thanks.

Idle Grouping = SWITCH(

    TRUE(),

    'Long Idle'[Long Idle %] <.05,".04",

    'Long Idle'[Long Idle %] >=.05 && 'Long Idle'[Long Idle %] <.10, ".05",

    'Long Idle'[Long Idle %] >=.10 && 'Long Idle'[Long Idle %] <.15, ".10",

    'Long Idle'[Long Idle %] >=.15 && 'Long Idle'[Long Idle %] <.20, ".15",

    'Long Idle'[Long Idle %] >=.20 && 'Long Idle'[Long Idle %] <.40, ".20",    

    'Long Idle'[Long Idle %] >=.40 && 'Long Idle'[Long Idle %] <.60, ".40",

    'Long Idle'[Long Idle %] >=.60 && 'Long Idle'[Long Idle %] <.80, ".60",

    'Long Idle'[Long Idle %] >.80, ".80",

    "OTHER")

[Uzi2019]

Hi @cheid_4838 

You Idle Grouping is sorting in correct order > like if you see every open catgeory hav proper sorting in Asc order.

So i Think you dont need to fix your idle grouping because your Main hierarchy is not sorted order.

You have to fix you Idle Subgrouping which is Text column.

In text column it will sort Aplabetically.. so you 10-20% will come first because of 1 2 3 4 5 number  sequence .

So sort order is 10,4,40

I hope I answered your question!

[Uzi2019]

Hi @cheid_4838 

try this calculated column

Order=

SWITCH(
TRUE(),
'Idle subgrouping' =  4% - 5% ,1,
'Idle subgrouping' = 10% - 20%, 2,

'Idle subgrouping' = 40% - 50%, 3,

         4)   ------- for other

I hope this might help you sort the order.

select your idle subgrouping

go to Column Tools tab then  click sort by column

select your sort order column

I hope I answered your question!

[cheid_4838]

I used this logic to create the subgroup, but get the below  error message when I sort by the order logic. 

Idle SubGroup = SWITCH(

    TRUE(),

    'Long Idle'[Long Idle %] <.05,"4% - 5%",

    'Long Idle'[Long Idle %] >=.05 && 'Long Idle'[Long Idle %] <.10, "4% - 5%",

    'Long Idle'[Long Idle %] >=.10 && 'Long Idle'[Long Idle %] <.15, "10% - 20%",

    'Long Idle'[Long Idle %] >=.15 && 'Long Idle'[Long Idle %] <.20, "10% - 20%",

    'Long Idle'[Long Idle %] >=.20 && 'Long Idle'[Long Idle %] <.40, "10% - 20%",    

    'Long Idle'[Long Idle %] >=.40 && 'Long Idle'[Long Idle %] <.60, "40% - 100%",

    'Long Idle'[Long Idle %] >=.60 && 'Long Idle'[Long Idle %] <.80, "40% - 100%",

    'Long Idle'[Long Idle %] >.80, "40% - 100%",

    "OTHER")

Order =

SWITCH(

TRUE(),

'Long Idle'[Idle SubGroup] =  "4% - 5%",1,

'Long Idle'[Idle SubGroup] = "10% - 20%",2,

'Long Idle'[Idle SubGroup] = "40% - 100%",3,

0)

[Uzi2019]

Hi @cheid_4838 

For your understanding you Idle sub grouping is sorting based on text column not Numeric so sorting order is 1,4,4 this is what it is following because of alphanumeric sorting .

Taking 1 element of every word then sort.

I hope this might give you clearity.

[cheid_4838]

That is what I am having issues with. I need these ranges put in the correct order, but nothing is working.  I have tried everything from using switch to create the order to grouping. Each time I get a circular dependency error message to not being able to compare text to numbers.  Not sure what else my options are.

[Uzi2019]

hi @cheid_4838 

can you change your numeric text 

Idle SubGroup = SWITCH(

    TRUE(),

    'Long Idle'[Long Idle %] <.05,"1.  4% - 5%",

    'Long Idle'[Long Idle %] >=.05 && 'Long Idle'[Long Idle %] <.10, "1.  4% - 5%",

    'Long Idle'[Long Idle %] >=.10 && 'Long Idle'[Long Idle %] <.15, "2.  10% - 20%",

    'Long Idle'[Long Idle %] >=.15 && 'Long Idle'[Long Idle %] <.20, "2.  10% - 20%",

    'Long Idle'[Long Idle %] >=.20 && 'Long Idle'[Long Idle %] <.40, "2.  10% - 20%",    

    'Long Idle'[Long Idle %] >=.40 && 'Long Idle'[Long Idle %] <.60, "3.  40% - 100%",

    'Long Idle'[Long Idle %] >=.60 && 'Long Idle'[Long Idle %] <.80, "3.  40% - 100%",

    'Long Idle'[Long Idle %] >.80, "4. 40% - 100%",

    "OTHER")

Try this it automatically sort without creating other sorting measure.

[cheid_4838]

I was thinking that, but trying to avoid putting a leading number to drive the sort.  Thanks for your help.

[Uzi2019 · Accepted solution]

Hi @cheid_4838 

Or you can have seperate table with idle subgrouping and sorting order.

create relationship between your idle sub grouping an dyou table sub grouping.

use idle subgrouping from newly created seperate table. make sure you apply sort by order step on idle sub groping (new table).

use only idle sub grouping from new table rest column from your old table.

I hope this might help you!

[cheid_4838]

Thanks for that suggestion. I was thinking that, but wanted to exhaust my efforts trying to find logic that would work.  Thanks for your help.

[Shravan133]

Try this:

SortOrder =
SWITCH(
TRUE(),
'Long Idle'[Long Idle %] < .05, 1,
'Long Idle'[Long Idle %] >= .05 && 'Long Idle'[Long Idle %] < .10, 2,
'Long Idle'[Long Idle %] >= .10 && 'Long Idle'[Long Idle %] < .15, 3,
'Long Idle'[Long Idle %] >= .15 && 'Long Idle'[Long Idle %] < .20, 4,
'Long Idle'[Long Idle %] >= .20 && 'Long Idle'[Long Idle %] < .40, 5,
'Long Idle'[Long Idle %] >= .40 && 'Long Idle'[Long Idle %] < .60, 6,
'Long Idle'[Long Idle %] >= .60 && 'Long Idle'[Long Idle %] < .80, 7,
'Long Idle'[Long Idle %] > .80, 8,
9 -- "OTHER"
)

After creating the SortOrder column:

1. Go to the Fields pane.
2. Select the Idle Grouping column.
3. In the Column Tools tab on the ribbon, click Sort by Column.
4. Select the SortOrder column.

This ensures the ranges in Idle Grouping are displayed in the correct order in visuals.

[cheid_4838]

I created this logic to create the subgroup and then the second logic to create the order. When I do this I get This error message.

Idle SubGroup = SWITCH(

    TRUE(),

    'Long Idle'[Long Idle %] <.05,"4% - 5%",

    'Long Idle'[Long Idle %] >=.05 && 'Long Idle'[Long Idle %] <.10, "4% - 5%",

    'Long Idle'[Long Idle %] >=.10 && 'Long Idle'[Long Idle %] <.15, "10% - 20%",

    'Long Idle'[Long Idle %] >=.15 && 'Long Idle'[Long Idle %] <.20, "10% - 20%",

    'Long Idle'[Long Idle %] >=.20 && 'Long Idle'[Long Idle %] <.40, "10% - 20%",    

    'Long Idle'[Long Idle %] >=.40 && 'Long Idle'[Long Idle %] <.60, "40% - 100%",

    'Long Idle'[Long Idle %] >=.60 && 'Long Idle'[Long Idle %] <.80, "40% - 100%",

    'Long Idle'[Long Idle %] >.80, "40% - 100%",

    "OTHER")

Sort Order 

Order =

SWITCH(

TRUE(),

'Long Idle'[Idle SubGroup] =  "4% - 5%", 1,

'Long Idle'[Idle SubGroup] = "10% - 20%", 2,

'Long Idle'[Idle SubGroup] = "40% - 100%", 3,

0)

[Shravan133]

create a column through power query(transform data) to avoid circular dependancy error.

• Open Power Query Editor 
• Add a new column for sorting:
  1. Go to the Add Column tab.
  2. Use a custom formula or conditional column logic.