Forum Discussion

cheid_4838's avatar
cheid_4838
Helper IV
1 year ago
Solved

Number range ordering

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 correc...
Need Help
  • Uzi2019's avatar
    Uzi2019
    1 year ago

    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!

     

     

Uzi2019's avatar
Uzi2019
Community Champion
1 year ago

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's avatar
cheid_4838
Helper IV
1 year ago

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)