Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Agent | Sales | Year | Product |
Sam | 1000 | 2019 | A |
Sam | 200 | 2017 | B |
Sam | 300 | 2019 | C |
Emily | 3000 | 2019 | C |
Karen | 1000 | 2018 | B |
Karen | 200 | 2019 | C |
Rank | Agent | Sales |
1 | Emily | 3000 |
2 | Sam | 300 |
3 | Karen | 200 |
Solved! Go to Solution.
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])
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
Hi Greg,
First of all thank you so much for always answering my questions 🙂
I managed to create the index via measure below:
Agent | Sales | Year | Product | Region |
Sam | 1000 | 2019 | A | West |
Sam | 200 | 2017 | B | West |
Sam | 300 | 2019 | C | West |
Emily | 3000 | 2019 | C | East |
Karen | 1000 | 2018 | B | West |
Karen | 200 | 2019 | C | West |
Rank | Agent | Sales | Region |
1 | Emily | 3000 | East |
1 | Sam | 300 | West |
2 | Karen | 200 | West |
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
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.
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])
I just added the filters in an ALLEXPECT and now it works perfectly - thank you so much!
Hooray! We got there!
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 🙂
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |