Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CAPEconsulting
Helper III
Helper III

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 formatPivot 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.

8 REPLIES 8
CAPEconsulting
Helper III
Helper III

@OwenAuger  @Phil_Seamark  @Mariusz  any suggestions please

 

Any suggestions please ???

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





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




@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.Capture.JPG

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

@PattemManohar and @dax any suggestions at all ?

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

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.

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors