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 

 

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

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's avatar
    Uzi2019
    Community Champion
    1 year ago

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

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

      • Uzi2019's avatar
        Uzi2019
        Community Champion
        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!