cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mohitchugh Occasional Visitor
Occasional Visitor

Ranking of Series

I want to customize the ranking of given below series value.

 

Series Values:  90, 50, 100, 40, 10, 30, 0, -50, -60, -10, 20, -5

 

We want the result should be like first highest -ve value set then the highest +ve value set and last zero.

 

Now like: -60,  -50,  -10,  -5,    100,   90,   50,   40,  30,   20,  10,      0

 Rank#       1       2      3     4       5      6     7      8     9    10    11     12

                 ~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~      ~~

                  -ve value set                      +ve value set                        Zero

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Ranking of Series

Hi @mohitchugh 

Create measures

Measure = SUM(Table1[series values])

Measure 1 =
SWITCH (
    MAX ( Table1[Column] ),
    "-", RANKX ( ALLEXCEPT ( Table1, Table1[Column] ), [Measure],, ASC, DENSE ),
    "+", RANKX ( ALLEXCEPT ( Table1, Table1[Column] ), [Measure],, DESC, DENSE ),
    "zero", 1
)


Measure 2 = MAXX(FILTER(ALL(Table1),Table1[Column]="-"),[Measure 1])

Measure 3 = IF(MAX(Table1[Column])="+",[Measure 2]+[Measure 1])

Measure 4 = MAXX(FILTER(ALL(Table1),Table1[Column]="+"),[Measure 3])

Measure 5 = IF(MAX(Table1[Column])="zero",[Measure 1]+[Measure 4])

Measure 6 = SWITCH(MAX(Table1[Column]),"-",[Measure 1],"+",[Measure 3],"zero",[Measure 5])


6.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 REPLY 1
Community Support Team
Community Support Team

Re: Ranking of Series

Hi @mohitchugh 

Create measures

Measure = SUM(Table1[series values])

Measure 1 =
SWITCH (
    MAX ( Table1[Column] ),
    "-", RANKX ( ALLEXCEPT ( Table1, Table1[Column] ), [Measure],, ASC, DENSE ),
    "+", RANKX ( ALLEXCEPT ( Table1, Table1[Column] ), [Measure],, DESC, DENSE ),
    "zero", 1
)


Measure 2 = MAXX(FILTER(ALL(Table1),Table1[Column]="-"),[Measure 1])

Measure 3 = IF(MAX(Table1[Column])="+",[Measure 2]+[Measure 1])

Measure 4 = MAXX(FILTER(ALL(Table1),Table1[Column]="+"),[Measure 3])

Measure 5 = IF(MAX(Table1[Column])="zero",[Measure 1]+[Measure 4])

Measure 6 = SWITCH(MAX(Table1[Column]),"-",[Measure 1],"+",[Measure 3],"zero",[Measure 5])


6.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.