Dynamic Rank Analysis Top 5 based on different measures

[aAmirkHan]

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]

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]

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]

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!!

[aAmirkHan]

Hi @shafiz_p ,

I am just one step away from achieving the desired output, but there is a sorting issue. Metric values are not coming in proper order.

It would be great if you advise on this.

However, when I try to do it sorting but later is gets changed.

Thank you

[shafiz_p · Accepted solution]

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

[Irwan]

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.