Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dynamic Rank with different Tables and Sort

Hello Everyone,   I hope this is not so tricky, before i start explaining, please have in mind that i already read like 10-20 post and tryed a lot of ways but with no success, and i hope i can find...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous 

I believe FIRSTNONBLANK/LASTNONBLANK functions both work for you.

 

Rank measure = RANKX (ALL('Sales LY'),LASTNONBLANK('Sales LY'[Sales Actual],0),,DESC)

 


Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hey Anonymous  Thanks this helps me a lot but looks like if i have like weekly Sales for example the same data for each year repeated 5 times like if this were for 5 different weeks ( i didnt upload the file like this :/) the formula does not work 😞

any suggestions?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

     

    I cannot picture how is your actual model look like exactly but if you need to rank sales in different weeks.

     

    You could first create a weeknumber column using WEEKNUM function on the date column.

    Then ALLEXCEPT the column as the context:

     

    Measure = RANKX(ALLEXCEPT(Table,Table[Weeknum]),CALCULATE(SUM(Table[Sales])),,DESC,Dense)

     

    BTW you can also replace FIRSTNOBLANK with Calculate(SUM.

     

    Paul Zheng

    Don't forget to hit THUMBS UP and mark it as a solution if it helps you!