Forum Discussion

PowerBI-Newbie's avatar
PowerBI-Newbie
Helper IV
1 year ago
Solved

Dynamic Index/Rank (1st, 2nd, 3rd) on Graphs

I have the following graph on 3 separate pages of a Power BI report showing the breakdown of a category and sub-category:     The idea is that each page will represent a location (dynamic) b...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    Here is  an example of using RANK  which allows you to rank by more than one column.

     

    R = 
var a = ADDCOLUMNS(ALLSELECTED('Table'[Location]),"c",CALCULATE(COUNTROWS('Table'),REMOVEFILTERS('Table'[Asset Type],'Table'[Sub-Type])))
return rank(DENSE,a,ORDERBY([c],DESC,[Location],ASC))

    is that what you are looking for?

     

     

PowerBI-Newbie's avatar
PowerBI-Newbie
Helper IV
1 year ago

Hi Anonymous, lbendlin,

Apologies if the question wasn't clear, here's some sample data to help:

DateLocationAsset TypeSub-Type
22/12/2024Location 1Asset Type 1Sub-Type 1
22/12/2024Location 1Asset Type 1Sub-Type 1
22/12/2024Location 1Asset Type 2Sub-Type 2
22/12/2024Location 1Asset Type 2Sub-Type 2
22/12/2024Location 1Asset Type 2Sub-Type 2
22/12/2024Location 2Asset Type 1Sub-Type 1
22/12/2024Location 2Asset Type 1Sub-Type 1
22/12/2024Location 2Asset Type 2Sub-Type 2
22/12/2024Location 3Asset Type 2Sub-Type 2
23/12/2024Location 1Asset Type 1Sub-Type 1
23/12/2024Location 1Asset Type 1Sub-Type 1
23/12/2024Location 1Asset Type 1Sub-Type 1
23/12/2024Location 1Asset Type 2Sub-Type 2
23/12/2024Location 1Asset Type 2Sub-Type 2
23/12/2024Location 1Asset Type 2Sub-Type 2
23/12/2024Location 2Asset Type 1Sub-Type 1
23/12/2024Location 2Asset Type 1Sub-Type 1
23/12/2024Location 2Asset Type 2Sub-Type 2
23/12/2024Location 2Asset Type 2Sub-Type 2
23/12/2024Location 2Asset Type 2Sub-Type 2
23/12/2024Location 2Asset Type 2Sub-Type 2
23/12/2024Location 3Asset Type 1Sub-Type 1
23/12/2024Location 3Asset Type 2Sub-Type 2

 

From the above table the graph will look like this:

 

 

A simple count of the number of rows against each Asset Type and Sub-Type taking all locations and dates into account. A breakdown of the above graph below in pivot format:

 

 

There are 3 graphs as mentioned in the initial question - 1st, 2nd and 3rd. What I'm looking for is when the user chooses a date from the slicer (which will be single selection only) then the graph for the 1st rank/index location for example will be dynamically filtered to show data for 1st rank/index location only - so using the above data then it'll be Location 1 when 22nd December 2024 is selected since it has a count of 5 so the graph will look like this:

 

When the user selects 23rd December 2024, there's a tie between Location 1 and Location 2 so I would want Location 1 to be considered 1st due to alphabetical order.

 

I hope the request/question is clear now.

lbendlin's avatar
lbendlin
Super User
1 year ago

 

For your second request you can change the formula to pick the TOPN(1) based on location name.

 

  • PowerBI-Newbie's avatar
    PowerBI-Newbie
    Helper IV
    1 year ago

    Hi lbendlin,

    Thank you for your response.

    Your solution works for 1st ranking (ish) - so when I change it to a graph it works for 22nd December:

     

    But when I change it to 23rd December it doesn't work since there's a tie:

    I would want Location 1 to be selected due to alphabetical order. How do I update your measure to cater for this?

     

    And also, how can I get 2nd ranking and 3rd ranking?

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      "I would want Location 1 to be selected due to alphabetical order. How do I update your measure to cater for this?"  

       

      As I mentioned use TOPN(1) and sort by name, or use RANK instead of RANKX.

       

      "And also, how can I get 2nd ranking and 3rd ranking?"

       

      I don't understand the question. Can you please elaborate or indicate a desired outcome based on the sample data you provided?

      • PowerBI-Newbie's avatar
        PowerBI-Newbie
        Helper IV
        1 year ago

        Hi lbendlin,

        Thank you for your response.

        With regards to your first comment, are you able to assist me with this? I'm not very good with DAX.

         

        As for your question then if the user was to select 22nd December then the graphs would look like this:

        1st Ranking - Location 1 has the highest count

        2nd Ranking - Location 2 has the 2nd highest count

        3rd Ranking - Location 3 has the 3rd highest count

         

        When the user selects 23rd December then the graphs would look like the following:

        1st Ranking - Location 1 - despite it being a tie it comes first due to alphabetical order

        2nd Ranking - Location 2 - despite it being a tie it comes second due to alphabetical order

        3rd Ranking - Location 3 has the 3rd highest

         

        I hope that is clear now.