Forum Discussion
Solved
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 ...
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
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.
Nathaniel
