Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

RANKX issue: dynamic index column with multiple filters

 

For dataset below, how can I create an index column or measure that indexes agents based on sum of their net sales, given year = 2019 and product = C without actually filtering any columns within Power Query?

 

I've read a lot of answers here but the solution either doesn't apply or won't work (eg. everything above 1500 is rank 1, 500 to 1000 is rank 2 etc.) so I would really appreciate your assistance. 

 

Thank you!

 

AgentSalesYearProduct
Sam10002019A
Sam2002017B
Sam3002019C
Emily30002019C
Karen10002018B
Karen2002019C

 

RankAgentSales
1Emily3000
2Sam300
3Karen200

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    It's very difficult to say without seeing your data but try this variation:

     

    Rank4 = 
VAR __agent = MAX([Agent])
VAR __table = SUMMARIZE(ALL('Table19'),[Agent],"__sales",SUM([Sales]))
VAR __table2 = ADDCOLUMNS(__table,"__rank",RANKX(__table,[__sales],,DESC,Dense))
RETURN
MAXX(FILTER(__table2,[Agent]=__agent),[__rank])

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    So you want a solution in Power Query? In DAX you would use RANKX. In Power Query you would ask for a lifeline from ImkeF.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    If you do want it in DAX, then this should work:

     

    Rank = 
VAR __year = MAX([Year])
VAR __product = MAX([Product])
RETURN
RANKX(FILTER(ALL('Table19'),[Year]=__year && [Product]=__product),[Sales],SUM([Sales]))

    See Table19, Page 13

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Greg,


      First of all thank you so much for always answering my questions :) 

       

      I managed to create the index via measure below:

      Rank = rankx(
      ALLNOBLANKROW(Table[Agent]),
      calculate(sum(Table[Sales])),,desc,dense)
       
      However, if I try to show the region assigned to the agent, instead of ranking the agents by sales and also showing their regions, it ranks agents within the region. For example, 
      AgentSalesYearProductRegion 
      Sam10002019AWest
      Sam2002017BWest
      Sam3002019CWest
      Emily30002019CEast
      Karen10002018BWest
      Karen2002019CWest
       
       becomes 
      RankAgentSalesRegion
      1Emily3000East
      1Sam300West
      2Karen200West

      Is there any way to alter my formula as to disregard regions? 
       
      Thank you in advance. 
       
      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        7 years ago

        OK, my original formula didn't have that issue, but I modified yours like this:

         

        Rank2 = 
VAR __table = CALCULATETABLE('Table19',ALLEXCEPT('Table19','Table19'[Agent],'Table19'[Year],'Table19'[Product]),ALLNOBLANKROW('Table19'[Agent]))
RETURN
rankx(
__table,
calculate(sum(Table19[Sales])),,desc,dense)

        See attached, Table19