cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CAPEconsulting Regular Visitor
Regular Visitor

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

Capture.JPGPivot 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 Regular Visitor
Regular Visitor

Re: RANKX excluding blanks

@OwenAuger  @Phil_Seamark  @Mariusz  any suggestions please

 

Community Support Team
Community Support Team

Re: RANKX excluding blanks

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 Regular Visitor
Regular Visitor

Re: RANKX excluding blanks

@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

CAPEconsulting Regular Visitor
Regular Visitor

Re: RANKX excluding blanks

Any suggestions please ???
CAPEconsulting Regular Visitor
Regular Visitor

Re: RANKX excluding blanks

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

Highlighted
Super User
Super User

Re: RANKX excluding blanks

@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 Datanaut !





CAPEconsulting Regular Visitor
Regular Visitor

Re: RANKX excluding blanks

@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

CAPEconsulting Regular Visitor
Regular Visitor

Re: RANKX excluding blanks

@PattemManohar and @dax any suggestions at all ?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 373 members 3,122 guests
Please welcome our newest community members: