Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Acamara
Frequent Visitor

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.

Acamara_0-1619465966841.png

 

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!

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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:

v-angzheng-msft_0-1619689627048.jpeg

Result:

v-angzheng-msft_1-1619689627056.jpeg

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.

View solution in original post

9 REPLIES 9
v-angzheng-msft
Community Support
Community Support

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:

v-angzheng-msft_0-1619689627048.jpeg

Result:

v-angzheng-msft_1-1619689627056.jpeg

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.

MURTAZA
Resolver I
Resolver I

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

Hi Murtaza.

Thanks for your help!

 

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

Acamara_0-1619470756486.png

 

 

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

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!

 

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

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

Fowmy
Super User
Super User

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Acamara
Frequent Visitor

Hi Fowmy.

Thanks for your advice and help.

Here it goes: https://1drv.ms/u/s!AnE8bWuyHjo_kl-So6sj1-QAMzoz?e=kPLDHC

My objetive seems to be pretty simple and I've tried some ready to go solutions from this forum but none have worked soo far.

 

Thanks again.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.