Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

RANKx - Dynamic based on Filter Context

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

 

 

 

Need Help
Show and Tell

3 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

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