Forum Discussion

SuchCT's avatar
SuchCT
Helper II
3 years ago
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 ...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    3 years ago

    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

Nathaniel_C's avatar
Nathaniel_C
Community Champion
3 years ago

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's avatar
Nathaniel_C
Community Champion
3 years ago

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's avatar
    SuchCT
    Helper II
    3 years ago

    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's avatar
      Nathaniel_C
      Community Champion
      3 years ago

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

       

      Thanks,

      Nathaniel

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    3 years ago

    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's avatar
      Nathaniel_C
      Community Champion
      3 years ago

      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