Product's Rank

[SLI]

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

Rank =

RANKX (

ALL( FACT_Production[ReferenceProduit]),calculate([THSalesp M-1],ALLEXCEPT(FACT_Production,FACT_Production[ReferenceProduit],FACT_Production[POS],FACT_Production[Date])),,DESC,Dense)

Here i share with you my sample of model

thank you in advance for your help

SLI

[PaulDBrown · Accepted solution]

@SLI 

Isn't this the same question we discussed here?

https://community.powerbi.com/t5/Desktop/How-to-find-out-if-a-product-is-in-the-top-20-of-last-month... 

or do you need further help?

[SLI]

Hi
It is the same ☺️ And your solution works very good
Thank you so much
Regards

[v-jayw-msft]

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.

[amitchandak]

Refer, how weekly rank has been compare

https://www.dropbox.com/s/0ec5u2nhxuo22eb/weeklysellerRank.pbix?dl=0

[amitchandak]

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/