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.

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
    Helper 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.