Forum Discussion

CAPEconsulting's avatar
CAPEconsulting
Helper III
6 years ago

RANKX excluding blanks

The following is a simpler version of my table that actually has very high volue of data and is sensetive data that I cannot share - so have made a dummy version below

 

Store CategoryStore IDIndicatorItemValue
AwesomeX001qs1include1
AwesomeX002qs1exclude2
AwesomeX003qs1include2
DangerousY001qs1 3
DangerousY002qs1include0
DangerousY003qs1 1
AwesomeX001qs2include0
AwesomeX002qs2 0
AwesomeX003qs2include3
DangerousY001qs2 5
DangerousY002qs2 8
DangerousY003qs2 7

 

I want to calculate rank of store ID based on store catgeory and by Indicator and ranking only when denominator was >1
So a Pivot table that can give me

Pivot table format

I have the following Measures

MeasureSum = SUM(Sumo[Value])

Numerator = CALCULATE( [MeasureSum] , FILTER( VALUES(Sumo[Item]),  Sumo[Item]= "Include" ))
Denominator = CALCULATE( [MeasureSum] , FILTER( VALUES(Sumo[Item]),  Sumo[Item]<> "Exclude" ))

Proportion = DIVIDE( Numerator, Denominator)

Ranking Proportion =IF( [Denominator] >1, [Proportion], BLANK())

Rank =  IF (NOT (ISBLANK ([Ranking Proportion])), RANKX( ALLSELECTED(Sumo[Indicator]), CALCULATE( [Ranking Proportion], ALLSELECTED( Sumo[Store Category])), , ASC, Dense))

 

Here I get ranks just fine but the in indicator where a store ID has blank gets a virtual rank of 1 (even though it appears blank) and the ranks start from 2 for the one that I really need to be rank 1.

 

Could you assist please in removing BLANK() from my RANKX.

Need Help
Show and Tell
Tips and Tricks

8 Replies

    • dax's avatar
      dax
      Community Support
      6 years ago

      Hi CAPEconsulting, 

      It seems that you want to exclude blank when ranking, right? If so, you could try below measure 

      Rank = IF (NOT (ISBLANK ([Ranking Proportion])), RANKX( FILTER(ALLSELECTED(Sumo[Indicator]), NOT(ISBLANK([Ranking Proportion]))), CALCULATE( [Ranking Proportion], ALLSELECTED( Sumo[Store Category])), , ASC, Dense))

      Best Regards,
      Zoe Zhi

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • CAPEconsulting's avatar
        CAPEconsulting
        Helper III
        6 years ago

        dax 

        I have tried this but this way it gives me ranks within the store categories. So each category gets its own rank 1 rank 2 etc. But that's not what I want. I want ranks for all of the dataset. So rank 1 needs to be the best in all store categories combined.

        Any other suggestions