Forum Discussion
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...
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
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!