Dynamic Rank Analysis Top 5 based on different measures

Hi,

In my Power BI report, I have created measures such as Revenue, Unit Sold, Total Invoice, and Operating Profit. I want to make a Rank Analysis of States like TOP 5 states which should work based on the selection of measures.

Can you please advise how to achieve such a requirement?

Thank you

shafiz_p
1 year ago
Since field value is dynamic and it changes by selection, you would not be able to sort for all. Let's say, you sort this by Unit Sold, it will sort but when change to other metric measure say revenue, sorting will change.

So to fix it, you need to use a column which is fixed. In this case Dynamic Rank could be used. See image below:
If you don't want to show DynamicRank, you can do a little trick and formating. See image where is actually DynamicRank measure present but you would not be able to see it.
This way I acheived sorting as well as maintained visual requirement.
To acheive this, just change name in the value section of DynamicRank by double clicking to single space. change font color to white. Re adjust column width.

Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

6 Replies

shafiz_p
Super User
1 year ago
Hi aAmirkHan First create a field parameter. I have 2 measure revenue and avgrev. see images below:

Try the below code for rank dynamically:
Dynamic Rank = VAR _selectedMetric = SELECTEDVALUE(Metric[Metric Order]) VAR RevRank = RANKX(ALLSELECTED(Customer), [Revenue], ,DESC, DENSE) VAR AvgRevRank = RANKX(ALLSELECTED(Customer), [AvgRev], ,DESC, DENSE) VAR Result = SWITCH( TRUE(), _selectedMetric = 0, AvgRevRank, _selectedMetric = 1, RevRank, BLANK() ) RETURN Result
Then create top5 measure as follows:
Top5 = IF([Dynamic Rank] <= 5, 1, BLANK())

Place dynamic rank measure in the table/matrix value field. Select table/matrix and go to filter pane and place top5 measure in filter this visual option. See image below:

Desired output:

Hope this helps!!
If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz
- aAmirkHan
  Helper III
  1 year ago
  Hi shafiz_p ,
  Thank you for the response. I tried to make it, but it is returning only rank, not value. Based on the selection metrics, I want to have dynamic value with the top 5.
  Can you please suggest what should be changed or how to achieve the desired output?
  
  Thank you
  - shafiz_p
    Super User
    1 year ago
    Perfect. You just forgot to place Field parameter in the matrix value section. See image below for my case:
    
    Placed the Metric Measure in the value section.
    
    You can keep you slicer setting to single select or change this line to something like that. In case no measure is selected in slicer, default will be 0:
    
    AR _selectedMetric = SELECTEDVALUE(Metric[Metric Order],0)
    
    Hope this helps!!
Irwan
Super User
1 year ago
hello aAmirkHan

is it possible to use filter pane?
additionally, you might want to use HASONEVALUE() if you want to show 5 top item when selecting the slicer and show all value when no slicer is selected.
Something like this,
Values =
var _Total = CALCULATE(SUM('Table'[Value]),ALL('Table'))
Return
IF(
HASONEVALUE('Table'[Category]),
SUM('Table'[Value]),
_Total
)

Hope this will help.
Thank you.