topic Re: Dynamically rank calculated measure based on different groupings in DAX Commands and Tips

Dynamically rank calculated measure based on different groupings

Anonymous — Thu, 24 Oct 2024 18:38:06 GMT

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

Re: Dynamically rank calculated measure based on different groupings

SachinNandanwar — Thu, 24 Oct 2024 20:32:37 GMT

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

Re: Dynamically rank calculated measure based on different groupings

Anonymous — Fri, 25 Oct 2024 13:26:33 GMT

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!

Re: Dynamically rank calculated measure based on different groupings

SachinNandanwar — Fri, 25 Oct 2024 16:30:41 GMT

Yes..

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

Re: Dynamically rank calculated measure based on different groupings

Anonymous — Mon, 28 Oct 2024 13:07:10 GMT

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!

Re: Dynamically rank calculated measure based on different groupings

SachinNandanwar — Mon, 28 Oct 2024 13:21:07 GMT

You can do it through field parameters.

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

Re: Dynamically rank calculated measure based on different groupings

Anonymous — Mon, 28 Oct 2024 13:41:22 GMT

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!

Re: Dynamically rank calculated measure based on different groupings

SachinNandanwar — Mon, 28 Oct 2024 13:48:11 GMT

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

Re: Dynamically rank calculated measure based on different groupings

Anonymous — Mon, 28 Oct 2024 14:05:53 GMT

Hi Sachin,

Try seeing here. Thank you!

Re: Dynamically rank calculated measure based on different groupings

SachinNandanwar — Mon, 28 Oct 2024 15:25:30 GMT

Can you grant access to the file ?

Re: Dynamically rank calculated measure based on different groupings

Anonymous — Mon, 28 Oct 2024 16:05:22 GMT

Hi Sachin!

Thanks so much - see if this link works here.

Re: Dynamically rank calculated measure based on different groupings

SachinNandanwar — Mon, 28 Oct 2024 20:27:15 GMT

Please check the screengrab.

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

Re: Dynamically rank calculated measure based on different groupings

Anonymous — Tue, 29 Oct 2024 13:31:05 GMT

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.

Re: Dynamically rank calculated measure based on different groupings

SachinNandanwar — Tue, 29 Oct 2024 23:51:11 GMT

Hi, Is this what you are looking for ?

Or this ?

Re: Dynamically rank calculated measure based on different groupings

Anonymous — Wed, 30 Oct 2024 17:44:18 GMT

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!

Re: Dynamically rank calculated measure based on different groupings

SachinNandanwar — Wed, 30 Oct 2024 20:58:51 GMT

Hi,
Please check

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

Re: Dynamically rank calculated measure based on different groupings

Anonymous — Mon, 04 Nov 2024 19:02:03 GMT

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)?

Re: Dynamically rank calculated measure based on different groupings

SachinNandanwar — Tue, 05 Nov 2024 05:04:30 GMT

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