Forum Discussion

Acamara's avatar
Acamara
Frequent Visitor
5 years ago
Solved

Creating a Ranking according to a date

Hello Guys.   I've been stuggling with creating Ranks for several products in different months.   I have a table which has different products, different months and serveral sales per month. What ...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, Acamara 

     

    Create a calculated column to extract the month in the date,

    _Month = MONTH ( 'Table'[Date] )

    Then, try to create a measure like below:

    _Rank measure =
RANKX (
    ALLEXCEPT ( 'Table', 'Table'[_Month] ),
    CALCULATE ( SUM ( 'Table'[Sales] ) ),
    ,
    DESC,
    DENSE
)

    I created a simple sample to illustrate this.

    Sample:

    Result:

    Please refer to the attachment below for details

     

     

    Is this the result you want? Hope this is useful to you

    Please feel free to let me know If you have further questions

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Acamara's avatar
Acamara
Frequent Visitor
5 years ago

Hi Murtaza.

Thanks for your help!

 

I fear that it may not worked. Here is what happnened when I've tried.

 

 

MURTAZA's avatar
MURTAZA
Resolver I
5 years ago

Acamaradid you create the Summarized table? You need to aggregate your sales to be able to rank them.

  • Acamara's avatar
    Acamara
    Frequent Visitor
    5 years ago

    Yeah I did. The table fAuxiliar... Is the summarized table.

  • Acamara's avatar
    Acamara
    Frequent Visitor
    5 years ago

    Hi Murtaza.

    Looks like you have solved it.

    So correct me if I am wrong, but it appears to me that my mistake was to try to use a Date as a criteria for the ranking, right? 

    Thanks again!

     

    • MURTAZA's avatar
      MURTAZA
      Resolver I
      5 years ago

      Acamara 

      Glad to hear that!

      Yes that is correct. You need to group columns to use in the RANKX function. If you use just the date, it will try to rank within that particular date and not month.