RANKx - Dynamic based on Filter Context

[Anonymous]

Hi,

I'm trying to use the RANKX function but I'm not able to get it to do what I want. Here's the data I'm working with:

I want to be able to Rank each Advertisers in each Quarters (e.g. FY19-Q2) based on the total spend for that Quarter. But I also want to be able to filter the results by Media Mix and State and for the values to be ranked accordingly. There's also a filter in place, Competitive Set, so this would also need to be taken into account.

The measure in place just now isn't working:

Nielsen - Spend, Ranked = RANKX(ALL('Spend (Nielsen)'),[Nielsen - Spend],,DESC)

Can anyone help me figure this out?

Thanks,

MarkJames

[v-xicai]

Hi @Anonymous ,

You may change the rank measure like DAX below.

Nielsen - Spend, Ranked= RANKX(FILTER(ALLSELECTED('Spend (Nielsen)'),'Spend (Nielsen)'[Advertiser]=MAX('Spend (Nielsen)'[Advertiser])&&'Spend (Nielsen)'[FY-Q]=MAX('Spend (Nielsen)'[FY-Q])),MAX('Spend (Nielsen)'[Nielsen - Spend]),,DESC)

You can go through the link https://docs.microsoft.com/zh-cn/dax/rankx-function-dax to learn more about the RANKX .

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai ,

Thanks for this but it's not doing what I want it to - all the rank values are coming back as 1.

It's the filter context that I'm struggling with so I'm not really following your ALLSELECTED section of the expression. The table itself will split the results by ADVERTISER and FY-Q. But I have 3 other filters I want to use that should dynamically affect the RANK for each ADVERTISERS for each FY-Q. These fields are COMPETITIVE SET, MEDIA MIX and STATE.

Any idea how I incorporate all of these elements into the expression?

Thanks,

MarkJames

[Anonymous]

Hi @v-xicai  any thoughts on what I need to do here?