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!

     

     

Shravan133's avatar
Shravan133
Super User
1 year ago

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

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's avatar
    Shravan133
    Super User
    1 year ago

    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.