Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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 To make it more clear, could you please post your expected output as well based on the above test data provided.
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.
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
@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.
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