Forum Discussion
Ranking rows with calculated column
Hello everyone,
I have a database that looks like this:
Person Week Sales
| A | 1 | 845 |
| B | 1 | 519 |
| C | 1 | 304 |
| A | 2 | 973 |
| B | 2 | 559 |
| C | 2 | 187 |
| A | 3 | 528 |
| B | 3 | 337 |
| C | 3 | 406 |
| A | 4 | 272 |
| B | 4 | 174 |
| C | 4 | 159 |
And in need to get the average sales per person considering only their top 2 weeks. For person C that would be 406 from week 3 plus 304 from week 1 divided by 2. AVG. 355.
However i can´t find a way to do it as when I use rankx to get the top weeks based on sales i always get a circular dependency with a formula along these lines
rankx ( allexcept ( table , person , week ) , Sales)
This is what i need
Person Week Sales Rank
| A | 1 | 845 | 2 |
| B | 1 | 519 | 2 |
| C | 1 | 304 | 2 |
| A | 2 | 973 | 1 |
| B | 2 | 559 | 1 |
| C | 2 | 187 | 3 |
| A | 3 | 528 | 3 |
| B | 3 | 337 | 3 |
| C | 3 | 406 | 1 |
| A | 4 | 272 | 4 |
| B | 4 | 174 | 4 |
| C | 4 | 159 | 4 |
Hi SuchCT
This gives the ranks of each person's weeks.Rank col = var _person = RankingSales[Person] var _rank = rankx(FILTER(ALL(RankingSales),RankingSales[Person]=_person),RankingSales[Sales]) return _rank
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
9 Replies
Hi SuchCT
If I understand you, you are trying to get the average of each person's top two weeks. If that is right, please try this.
On the left is the original table, on the right is the table with the measure.My table name is Ranking Sales
Average of Top 2 weeks = var _person = MAX(RankingSales[Person]) var _week = MAX(RankingSales[Week]) var _calcMax =CALCULATE(MAX(RankingSales[Sales]),FILTER(ALL(RankingSales),RankingSales[Person]=_person)) var _calcMaxWeek= CALCULATE(MAX(RankingSales[Week]),FILTER(ALL(RankingSales),RankingSales[Sales]=_calcMax &&RankingSales[Person]=_person)) var _calcNextMax = CALCULATE(MAX(RankingSales[Sales]),FILTER(ALL(RankingSales),RankingSales[Person]=_person && RankingSales[Week]<>_calcMaxWeek)) var _aver = DIVIDE(_calcMax+_calcNextMax,2) return _aver
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielHi SuchCT ,
Sounds good. I will think about making it scalable. I believe this solves your original query.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielHi SuchCT
This gives the ranks of each person's weeks.Rank col = var _person = RankingSales[Person] var _rank = rankx(FILTER(ALL(RankingSales),RankingSales[Person]=_person),RankingSales[Sales]) return _rank
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathanielthanks, that works great so I´ll mark it as the solution. Just one more thing, I'm trying to resolve ties. In the base some person had the exact same sales for two different weeks, I'd need for one of them to have one rank and the next rank for the other week
