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