Ranking rows with calculated column

[SuchCT]

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

[Nathaniel_C]

Hi @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.
Nathaniel

[Nathaniel_C · Accepted solution]

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

[SuchCT]

thanks, 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

[Nathaniel_C]

Hi @SuchCT ,
You are welcome!
With regards to the additional question, would you give me an example, and expected outcome?

Thanks,

Nathaniel

[Nathaniel_C]

Hi  @SuchCT 
Here is a scalable value

average based on rank col = 
var _howManyWks = +3  //This is hard coded , but you could bring in from a measure

var _person = RankingSales[Person]

var _rank = rankx(FILTER(ALL(RankingSales),RankingSales[Person]=_person),RankingSales[Sales])

var _sumXwks = CALCULATE(SUM(RankingSales[Sales]), FILTER(ALL(RankingSales), RankingSales[Rank col] <=_howManyWks && RankingSales[Person]=_person))

var _aver = DIVIDE(_sumXwks,_howManyWks)

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

[Nathaniel_C]

Hi @SuchCT ,

And if you only wanted to show the value 1 time for each person you could put an IF statement in the return.  Here I matched it with the ranked week equal to the number of weeks.

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

[Nathaniel_C]

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

[SuchCT]

This works great but i'd need it to be a column as to use it on a chart as an x-axis, is that possible?

[SuchCT]

Thanks, I'll se if it aplplies and let you know. Also i'll check if I can make it scalable and work for the top n weeks too