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 I need is to create a measure that determines a Rank for each product according to its Sales per month. So, I would have a different rank per product per month.

 

With this rank, I'd like to create another measure that filters the top 10 products according to the period I've selected in a slicer.

 

I am pretty confident that this is something easy, but I've been figthing agains pbi since last Wendnesday without success.

Thanks for your Help!

 

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.

9 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Acamara 
    Does your model have a dates table ?

    Please share data in CSV or Excel format, it will help me create a solution faster. You can save in OneDrive or any other cloud drive and share the file link here.

     

  • MURTAZA's avatar
    MURTAZA
    Resolver I
    5 years ago

    Hi

     

    If you would like to rank month-wise, then I would suggest doing the following:

     

    Create a new column, extracting the month from the date.

     

    Next, create a SUMMARIZE table, go to the modeling tab, click the new table and add the following:

    TableSummarize = SUMMARIZECOLUMNS(‘Table’[Product], ‘Table’[Month], "Total Sales", SUM(Table[Sales]) )

     

    Now in above table, add new calculated field for rank:

     

    Rank = RANKX(
	 Filter(
	  TableSummarize, 
	  TableSummarize[Month] = EARLIER(TableSummarize[Month])
	  ),
	  TableSummarize[Total Sales],,
    DESC,Dense
)

     

    Let me know if this works. Thanks

    • 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.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    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.