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?

     

     

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.