Dynamic Rank with different Tables and Sort

[Anonymous]

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 an answer thanks to you guys. So here we go:

I have 3 tables (Last Year Sales, This Year Sales and Stores Details) all joined by the Store Code. This is the table is have now displayed:

but i want  to have a Ranking like this:

AND, ( I think this is the tricky part) i want to sort descending by Sales Actual like this:

The table above is the final result i expect, at this point some of you may be have some doubts about the data structure that i will clarify now:

Why i have 2 Separated Table sales ?

Because data is really big and actually  i have units sold, gross margin, stocks, etc

Why i have a Store Details Table?

Because i have like 50 columns of store details ( this is just a sample of them)

Why theres no sales on some Stores ?

Because some stores may be closed during This year and some other might be New Ones

Some of the solutions to this topic are for when Store Details and Sales Actual/LY are in one table only, wich is not my case, thats why im posting this.

Im attaching a sample file for those who wanna give it a try 

https://drive.google.com/file/d/16voBO-NSn0KsB-yjgyGtr3Lpp9jsyjME/view?usp=sharing 

[Ashish_Mathur]

Hi,

My suggestion would be to append the sales datasets into a single dataset first.  However for doing so, we must have a Date column in each sales dataset so that we can use the Date and Time intelligence functions.  So if you have a Date column in both sales datasets, then share that revised PBI file.

[Anonymous]

Hi @Ashish_Mathur  unfornately this won work for me because as i explained datas are really big so apending this will made my refresh time to increase considerable ( we have already tryed this) 

[Anonymous]

@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]

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]

@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!

[VasTg]

@Anonymous 

Is serial no the rank? If so, you want the ranking in ascending but show the Sales Actual descending  order? 

When you sort the Sales Actual in descending order in the table, it will sort the serial no column too.

[Anonymous]

Hi @VasTg  Yes the Seriall No is the Rank, correct i want the Top Sales ( the one with bigger number ) to be TOP 1