cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: RANKX issue: dynamic index column with multiple filters

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

---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
Highlighted
Super User IX
Super User IX

Re: RANKX issue: dynamic index column with multiple filters

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.


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IX
Super User IX

Re: RANKX issue: dynamic index column with multiple filters

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


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Anonymous
Not applicable

Re: RANKX issue: dynamic index column with multiple filters

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. 
 
Super User IX
Super User IX

Re: RANKX issue: dynamic index column with multiple filters

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

 

 


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Anonymous
Not applicable

Re: RANKX issue: dynamic index column with multiple filters

I'm not sure why the formula isn't working, even without putting in region. 

It starts with the first 50 all being 1, and at the end goes to 20083 even though there are 283 rows in total. 

 

rank1.PNGrank2.PNG

Highlighted
Super User IX
Super User IX

Re: RANKX issue: dynamic index column with multiple filters

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

---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Anonymous
Not applicable

Re: RANKX issue: dynamic index column with multiple filters

I'm sorry, still doesn't work. The ranking isn't based on highest net sales (at least not the one after product & fiscal year filtered anyway).

Thank you for your time! I will keep looking 🙂 

Highlighted
Anonymous
Not applicable

Re: RANKX issue: dynamic index column with multiple filters

I just added the filters in an ALLEXPECT and now it works perfectly - thank you so much!

Highlighted
Super User IX
Super User IX

Re: RANKX issue: dynamic index column with multiple filters

Hooray! We got there!


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors