Forum Discussion

ValentinB's avatar
ValentinB
Frequent Visitor
5 years ago

Help on RANKX with Slicers

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. 

 

 

 

Need Help
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      ValentinB
      Frequent Visitor
      5 years ago

      Hello Anonymous ,

       

      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.

       

       

    • ValentinB's avatar
      ValentinB
      Frequent Visitor
      5 years ago

      Anonymous ,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's avatar
      ValentinB
      Frequent Visitor
      4 years ago

      Good day Anonymous ,

       

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