Forum Discussion

PowerBI-Newbie's avatar
PowerBI-Newbie
Icon for Helper IV rankHelper 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
Icon for Helper IV rankHelper 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
    Icon for Super User rankSuper 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
      Icon for Helper IV rankHelper 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
        Icon for Super User rankSuper 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, PowerBI-Newbie 

    Thank you very much for your reply and the data provided. I've updated the measures as follows:

    rank = RANK(
    DENSE,
    ALLSELECTED('Table'),
    ORDERBY([Count],DESC,'Table'[Sub-Type],ASC)
)

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

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

      Hi Anonymous,

      Thank you for your response.

      The user will have a number of filters other than Location to filter data with - the user won't be selecting the location, it should be selected for them based on what graph they're looking at. Similar to what Ibendlin has produced where he created a measure to calculate the highest count (1st rank) and applied that measure to the visual to filter only the location with the highest count.