Help on RANKX with Slicers

[ValentinB]

Good day all,

As it would seem there are many topics regarding the RANKX function, topics that I have visited and tried certain solutions but with no result, I am but forced to bother with this topic...

I am trying to make an "IndividualReport" based on an SQLDatabase where i have the following collumns.

• Name
• Date
• Fuel Taken
• Fuel Planned
• Reason

Based on the columns above I have created the following

• In PowerQuery I have created a column named "Extra Fuel" which is the difference between Fuel Taken and Fuel Planned
• a Measure named "% Extra Fuel Caried" = SUM(Table[Extra Fuel Carried]) / SUM(Table [Fuel Planned])

Based on the above I've tried creating

• a RankPosition based on % Extra Fuel Caried

RankPosition = CALCULATE(

RANKX( ALL(Table[Name]),[% Extra Fuel Carried],,ASC,Dense),

ALLSELECTED(Table[Date].[Date]),ALLSELECTED(Table[REASON]) )

• and also a TotalRanking (distinctcount of Name), to have a sense for the ranking of each individual (5 out 93/ 1 out 96 and so on)

TotalRanking = CALCULATE ( DISTINCTCOUNT(Table[Name]), ALLSELECTED(Table[Date].Date]), ALLSELECTED(FDM_OFP_Fuel[REASON]), ALL(FDM_OFP_Fuel[CrewName]))

In the report, besides different visuals (bar charts etc) I have added some slicers

• Name (to chose from the list to see individual scenario)
• Reason (to check multiple reasons for extra fuel)
• Date

The issues comes with the Date slicers. If the whole range of Date is selected (Jan - July 2021), the ranking work perfectly. The moment I change the date values, lets say whole April of 2021, the RankPosition and TotalPosition return gibberish values like

• the RankPosition has no sense to the reality of April
• the TotalRanking returns different values for each individual selected when the Distinct values in April is 93 Names and I would have expected to always return 93 for the month of April, no matter what individual Name I've selected

Note: Not sure if this is helpful, but in the SQLDatabase there are some items with errors that I have filtered in PowerQuery so that they wont be available when creating the report and visuals.

Once again, sorry for re-opening this topic, but after many tries I have lost all hope and any help in understanding the issue would be great. 

[v-xiaoyan-msft]

Hi @ValentinB,

According to your description, have you checked Table[Date]?

if you want to slice by date, you'll need to create separate dimensions for Year, Quarter, etc. so that you can include them in the ALLSELECTED() statement.

kindly share your sample data and expected result to me if you don't have any confidential Information. 

Article for reference:How to provide sample data in the Power BI Forum 

Best Regards,

Caitlyn Yan

[ValentinB]

Good day @v-xiaoyan-msft ,

By any chance were you able to access the documents from the link ?

[ValentinB]

@v-xiaoyan-msft ,one thing that I forgot to mentioned, in order to verify the whole period I have made a List Visualization with the values Name2 / %Extra Fuel/ Rank Position / Total Ranking.

Note: This visualisation is not altered by the slicer Name2 (using the "Edit Interactions" from the Format Tab)

By checking the List, I can see that it works for the whole period, but the moment I change the period it still kind of works but for same individual it skips a value of the Rank Position (see below photo) where for e.g. Rank 1 does not exist and the max value of Rank Position is 102 out of Total Ranking of 101...

Again, something that I dont seem to find the "why" in it.

[ValentinB]

Hello @v-xiaoyan-msft ,

I was just about to upload to my post a .pbix and a excel file with the data in order to be more usefull and then I saw your reply.

Below is a link to google drive with both files.

https://drive.google.com/drive/folders/1tetsnfb5xIJtfUthOs66n5ukeH5ZctH2?usp=sharing

As mentioned, when the slicer with the Date is selected for all the period, the measure for "Rank Position" and "Total Ranking" works ok. The moment I've selected a different period (be it a whole month, or 15 may to end of July), Rank Position and Total Ranking are constantly changing for each individual "Name2" and dont reflect the reality of the period.