Dynamically rank calculated measure based on different groupings

[mbmcdonald2]

Hi All, 

I'm having some trouble getting my rank to work properly and was hoping for some assistance. As you can see below, my Industry Per Caps Rank column is not what you would expect. I'd like the per caps rank for product A to be 1, product B to be 2, and so on. My formula is below as well. Does anyone know what I may be doing wrong here? I also want the ranking to update dynamically based on the slicer I'm using (e.g., instead of product, rank by manufacturer, etc). Any help would be greatly appreciated!!

Industry Per Caps Rank = rankx(allselected('Nielsen (excl. dist)'),[Per Caps],,DESC,Dense)

Product	Industry Per Caps	Industry Per Caps Rank
A	2.55	2
B	1.86	5
C	1.68	7
D	1.62	7
E	1.55	7
F	1.54	7
G	1.19	11
H	0.77	16
I	0.58	17
J	0.48	19
K	0.46	19
L	0.4	21
M	0.26	28
N	0.07	82

[SachinNandanwar]

DynamicRank =
RANKX (
    ALLSELECTED ( 'product_industry_per_caps' ),
    CALCULATE ( MAX ( 'product_industry_per_caps'[Product] ) ),
    ,
    ASC,
    DENSE
)

[mbmcdonald2]

Hi Sachin!

Thanks so much for your quick response. Would there be a way to build the formula to be able to swap out [product] column for other columns? For example, let's say I want to see per caps figures by [region] instead of [product]. Is this possible? I appreciate your insight!

[SachinNandanwar]

Yes..

DynamicRank =
RANKX (
    ALLSELECTED ( 'product_industry_per_caps' ),
    CALCULATE ( MAX ( 'product_industry_per_caps'[Region] ) ),
    ,
    ASC,
    DENSE
)

[mbmcdonald2]

Hi Sachin,

I tried your approach and it's still looking like it's not ranking correctly. Any further guidance would be greatly appreciated. Thank you!

[SachinNandanwar]

Can you share the PBI file ?If you can then please share it on Google drive or One drive

[mbmcdonald2]

Hi Sachin, 

Try seeing here. Thank you! 

[SachinNandanwar]

Can you grant access to the file ?

[mbmcdonald2]

Hi Sachin!

Thanks so much - see if this link works here. 

[SachinNandanwar]

Please check the screengrab.

PBI file uploaded here : https://easyupload.io/tl3qnh

[mbmcdonald2]

Hi Sachin, Thanks so much for the help here. Is there a way to build out this rank in the original Nielsen(excl. dist) table? I need this ranking/visual to adjust with different filters brought in. For example, if I added [Zone] as a filter, I'd like for this to update based on each zone. 

[SachinNandanwar]

Hi, Is this what you are looking for ?


Or this ?

[mbmcdonald2]

Hi Sachin! Thank you so much for your ongoing help. I'm looking for the bottom solution (but built with the ability to view by: category, sub-category, zone, channel, manufacturer) - essentially all the attribute fields listed in Nielsen (excl. dist) table. Do you know if there is a way to do this? Thank you!

[SachinNandanwar · Accepted solution]

Hi,
Please check


PBI File : https://easyupload.io/fxanxw

[mbmcdonald2]

Hi Sachin!

Thanks so much for the response. I'd like to know if there is a way to set up the formula to be able to swap out the column (e.g., [region], [sub-category]) dynamically based on the visual (without needing to write a separate formula for each rank). Thank you!

[SachinNandanwar]

You can do it through field parameters.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

[mbmcdonald2]

Thanks so much, Sachin! Do you know if there is a way to set up a parameter for the whole dashboard page (as opposed to just one visual)? 

[SachinNandanwar]

Please create a new topic for new requirements.Different requirements are getting jumbled up in one topic.