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?

     

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thank you lbendlin 

Hi, PowerBI-Newbie 

Based on your description, I've created the following sample data:

Create a rank measure:

 

rank = 
RANK(DENSE,ALLSELECTED('Table'[Column1]),ORDERBY([Count Measure],DESC))

 

Put it in the tooltips of the chart:

I've provided the PBIX file used this time below. You can check out the following article about Rank:

Introducing the RANK window function in DAX - SQLBI

 

 

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.