RANKX excluding blanks

[CAPEconsulting]

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 Category	Store ID	Indicator	Item	Value
Awesome	X001	qs1	include	1
Awesome	X002	qs1	exclude	2
Awesome	X003	qs1	include	2
Dangerous	Y001	qs1		3
Dangerous	Y002	qs1	include	0
Dangerous	Y003	qs1		1
Awesome	X001	qs2	include	0
Awesome	X002	qs2		0
Awesome	X003	qs2	include	3
Dangerous	Y001	qs2		5
Dangerous	Y002	qs2		8
Dangerous	Y003	qs2		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.

[CAPEconsulting]

@OwenAuger  @Phil_Seamark  @Mariusz  any suggestions please

 

[CAPEconsulting]

Any suggestions please ???

[PattemManohar]

@CAPEconsulting  To make it more clear, could you please post your expected output as well based on the above test data provided.

[CAPEconsulting]

@PattemManohar thnaks for replying. Below is the hypothetical outcome that I want in essense - Rank based on measure called [Ranking Proportion] of all all StoreIDs amongst ALL(Store Category). Where [Ranking Proportion] is Blank(), for that store for the respective indicator Rank is alos Blank() and Blanks are excluded in the ranking list. Hope this makes sense.

My current measure  Ranks=IF( NOT( ISBLANK( [Ranking Proportion] )), RANKX( FILTER(ALL(Sumo[Store ID]), NOT( ISBLANK( [Ranking Proportion] ))), CALCULATE( [Ranking Proportion], ALL( Sumo[Store Category])), , ASC, Dense) )

works perfectly but only when I have just the Store ID in the pivot table. The moment I add Store Category to my Pivot Table it only ranks within the Store Catgeories, whereas I want universal ranking not just within the Store Categories. I have used ALL( Sumo[Store Category] in my CALCULATE statement but the output is still giving me ranks within the Store Category

[CAPEconsulting]

@PattemManohar and @dax any suggestions at all ?

[CAPEconsulting]

@Zubair_Muhammad @AlbertoFerrari @marcorusso @MattAllington any suggestions anyone please

[dax]

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]

@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