Fighting with RANKX function

[Ppivot]

Hello Guys!!

I´ve been fighting for all day long with the function RANKX and I can´t be able to find a solution:

The function is simple but it doesn´t work as I expected:

I want to do a ranking of Sales by ID, but (here it´s where i found the problem) the Pivot table have to accept other references (rows) without modify the Rankx function, such as Client name and Country,...


If I only select into the table the ID the result is correct:

	Product 1		Product 2
ID	NetSales	Rank	NetSales	Rank
60322996	98.216 €	30	1.476.888 €	1
60081106		426	513.356 €	2
60301834	408.914 €	1	77.795 €	62
60304308	7.343 €	226	417.174 €	3
60308978	3.569 €	272	393.316 €	4

<tbody>
</tbody>

However when I introduce another Row field the Rankx stops working properly, and I only get the correct result if i look at the ID Total:

			Product 1		Product 2
ID	Client	Country	NetSales	Rank	NetSales	Rank
60322996	Client 1	Spain	98.216 €	1	1.476.888 €	1
60322996 Total			98.216 €	30	1.476.888 €	1
60081106	Client 2	England		1	513.356 €	1
60081106 Total				426	513.356 €	2
60301834	Client 3	USA	408.914 €	1	77.795 €	1
60301834 Total			408.914 €	1	77.795 €	62
60304308	Client 4	China	7.343 €	1	417.174 €	1
60304308 Total			7.343 €	226	417.174 €	3
60308978	Client 5	Spain	3.569 €	1	393.316 €	1
60308978 Total			3.569 €	272	393.316 €	4

<tbody>
</tbody>

The function:

Rank := RANKX ( ALL(Table[ID]) , SUM(Table[Net_Sales]) )

So Would it be possible to get the value from the Id subtotal in the row?

Thanks!!!

 

[Matt Allington]

This is a guess - I really need to test it but I am on my phone. Try wrapping the current measure inside this.

=calculate( your current formula here, ALLexcept(table,table[id]))

 

[Ppivot]

This is a guess - I really need to test it but I am on my phone. Try wrapping the current measure inside this.

=calculate( your current formula here, ALLexcept(table,table[id]))

Thank you Matt, it almost work but not completely. I mean, it works great with totals however when I introduce the Product Category in the Columns, the result is not correct.

I attached the file to make it easier.

https://www.dropbox.com/s/68dlglqf606wv6d/Rank.xlsx?dl=0

Thank you

 

[Ppivot]

Thank you Matt, it almost work but not completely. I mean, it works great with totals however when I introduce the Product Category in the Columns, the result is not correct.

I attached the file to make it easier.

https://www.dropbox.com/s/68dlglqf606wv6d/Rank.xlsx?dl=0

Thank you

Nor with the Year, it´s seems that doesn't be affected by the filters (Year and Category)

 

[Ppivot]

This is a guess - I really need to test it but I am on my phone. Try wrapping the current measure inside this.

=calculate( your current formula here, ALLexcept(table,table[id]))

This is a guess - I really need to test it but I am on my phone. Try wrapping the current measure inside this.

=calculate( your current formula here, ALLexcept(table,table[id]))

I think I have it, thank you a lot for your Tip!!!!

The formula: (If it can help to others)


Calculate(

RANKX( ALL(Table[ID] , SUM(Table[Net Sales] ) ,
ALLEXCEPT( Table, Table[ID], Calendar, Category )
)


In the ALLEXCEPT you have to put all the filters that you wanna still be affected in the Pivot Table, that's why I've put it the Calendar and Category Tables.