Forum Discussion
Product's Rank
hi
i have 3 dimension's tables (dim_time, dim_product and dim_pointofsale) and 1 fact table (fact_production) in wich i have sales by product, date and point of sale
i have calculated THSalesp M-1 = calculate (sum (fact_production [sales]), previousmonth (dim_time [date]))
i would like to have the position (rank)of my product compared to Sales previous month per date and point of sale
is it possible with dax
i have used this formula but the result is not correct
Here i share with you my sample of model
thank you in advance for your help
SLI
Isn't this the same question we discussed here?
or do you need further help?
5 Replies
Isn't this the same question we discussed here?
or do you need further help?
- Hi
It is the same ☺️ And your solution works very good
Thank you so much
Regards
Refer, how weekly rank has been compare
https://www.dropbox.com/s/0ec5u2nhxuo22eb/weeklysellerRank.pbix?dl=0
Create measure like these and use rank on top of that. Use calendar
MTD = CALCULATE(SUM(Table[Quantity]),DATESMTD('Date'[Date]))
LMTD = CALCULATE(SUM(Table[Quantity]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))//Rank
Rank Last = RANKX(ALL(Sheet1[Seller Name]),[MTD],,DESC,Dense)
Rank Last = RANKX(ALL(Sheet1[Seller Name]),[LMTD],,DESC,Dense)To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Hi SLI ,
Try split your measure as below and see if the result is what you want.
Measure 1 = CALCULATE(SUM(FACT_Production[THSales]),ALLEXCEPT(FACT_Production,FACT_Production[ReferenceProduit],FACT_Production[POS],FACT_Production[Date])) Measure 2 = RANKX(ALL(FACT_Production),[Measure 1],,DESC,Dense)Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
