Forum Discussion

dvhez's avatar
dvhez
Helper II
7 years ago
Solved

Sort values (rows) by categories (e.g. months)

Data looks like:

 

 

Year    Month    Value

2018      Jan          7
2018      Jan          1
2018      Jan          5
2018      Jan          9
2018      Jan          2
2018      Jan          4
2018      Feb          6
2018      Feb          2
2018      Feb          9
2018      Feb          4
2018      Feb          8
2018      Feb          1

 

Is there a way to sort this table as follows? (Lowest to highest, keeping month order)

 

2018      Jan          1
2018      Jan          2
2018      Jan          4
2018      Jan          5
2018      Jan          7
2018      Jan          9
2018      Feb          1
2018      Feb          2
2018      Feb          4
2018      Feb          6
2018      Feb          8
2018      Feb          9

My objective is to average the lowest 2 values, then the next 2 and so on... but maintaining their categories (months)

 

2018      Jan          1.5          Bottom 2
2018      Jan          4.5          Mid 2
2018      Jan          8             Top 2
2018      Feb          1.5          Bottom 2
2018      Feb          5             Mid 2
2018      Feb          8.5          Top 2

Thanks in advance!

 

  • dvhez,

     

    You may add the following calculated columns.

    Column =
    RANKX (
        FILTER (
            Table1,
            Table1[Year] = EARLIER ( Table1[Year] )
                && Table1[Month] = EARLIER ( Table1[Month] )
        ),
        Table1[Value],
        ,
        ASC,
        DENSE
    )
    
    Column 2 =
    DIVIDE ( EVEN ( Table1[Column] ), 2 )
    

1 Reply

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support

    dvhez,

     

    You may add the following calculated columns.

    Column =
    RANKX (
        FILTER (
            Table1,
            Table1[Year] = EARLIER ( Table1[Year] )
                && Table1[Month] = EARLIER ( Table1[Month] )
        ),
        Table1[Value],
        ,
        ASC,
        DENSE
    )
    
    Column 2 =
    DIVIDE ( EVEN ( Table1[Column] ), 2 )