Dynamic ranking of a parameter value

[czaldumbide]

Hi!

Would appreciate help figuring out how to create a measure that calculates the dynamic ranking of a parameter value.

Let me provide context. I have a 'Transactions' table with columns <ID>, <Weeknum>, <Amount>, <Category>. I'm interested in looking at the aggregate amount per week per ID, and its ranking. My   report will have a slicer filter where the user can select different categories, therefore my grouped table becomes dynamic. Additionally,  the user is able to input a value as a parameter that represents a weekly aggregate amount. Let's call that parameter value the 'threshold'. The objective is to figure out what rank is this threshold out of the grouped table. 

Example of original 'Transactions' table

ID	Weeknum	Amount	Category
111	30	100	A
111	30	50	B
111	31	200	B
222	31	150	A
222	31	200	A
222	32	300	A

Example of Grouped Table and ranking if filtered by Category A

ID	Weeknum	Weekly Amount	Ranking ASC
111	30	100	1
222	31	350	3
222	32	300	2

So if the threshold selected = 300, then my ranking measure should return 2.

Was thinking of using RANK.EQ function with threshold as the 'number' argument, but I'm stuck at the 'ref' argument. I can't pass a summarized table as the ref argument as it gives me the error that the base table can't be found. Please let me know how can I modify this to achieve the desired result.

[v-pgoloju]

Hi @czaldumbide,

Just a gentle reminder  has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @czaldumbide,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution  it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @czaldumbide,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[lbendlin]

There's no need for a summarized table - as you already have the filter context based on your category filter.

And then you can sneak in the threshold value as needed - as the "value"  optional parameter for RANKX.

[czaldumbide]

I'm still having trouble with the ranking function when slicers are applied to the report page. 

To make this exercise easier I have attached a picture of my report page and a sample PBI file. The columns highlighted in red are the ones not working properly yet. Would appreciate any tips on  how to modify these measures. 

Rank = 
RANKX(ALLSELECTED(Sales[WeekNum], Sales[ClientID]), CALCULATE([Sales]),, ASC)

------ this measure should return the position of the [sales] measure grouped by weeknum and client ID in ascending order, taking in account the filters from the slicer. So rank should have values from 1 to 138. 

Percentile = 
([Rank] -1)/ [Count All Selected Grouped by week and client]​

------ Should return the percentile of the corresponding [sales] measure based on the selected unique combinations of weenum and clientID, again taking in account filters from slicers. 

Closest Amount to Parameter = 
var threshold = Parameter[Parameter Value]
var closest_above = MINX(FILTER(Sales, Sales[Amount]>= threshold), Sales[Amount])
    MINX(FILTER(Sales, Sales[Amount]>= threshold), Sales[Amount])
var closest_below =
    MAXX(FILTER(Sales, Sales[Amount] <= threshold), Sales[Amount])
return if(abs(threshold-closest_above) < abs(threshold-closest_below), closest_above, closest_below)

---- Should return closest value to grouped table (in this case closest would have been 13400, instead of 15000). Currently it is returning closest value to original [sales] column, rather than [sales] measure grouped by clientID and weeknum. 

Rank of Parameter = 
RANKX( ALLSELECTED(Sales[WeekNum], Sales[ClientID]), CALCULATE([Sales]), [Closest amount to parameter],ASC)​

-----  should return the position in relation to sales grouped by weeknum and clientID. Basically if you look at the table underneath, what would the parameter value's position be. 

 Link to pbix file