Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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.
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.
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.
@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.
@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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |