I'm new to Power Bi Desktop. I'm trying to rank a text variable with respect to a decimal variable. I used RANKX function for this. Its working even for filters as well. But I'm getting problems when decimal variable becomes negative. Its working fine for positive decimals. For negative decimals the rank is showing as a different number from the rank that is shows for zero. Ideally it should be increased by 1. Then again after showing some random number for -0.01 the rank is increaing for -0.02 and is working perfectly fine.
For positive decimals set ranking is fine and for negative decimal set ranking is good. Its just that the ranks dont match for both of them, Ideally rankings should just increase as we go from positive to negative.
It seems than the problem is when in ranking you use measure that can return negative value. Here small example: Table: "Shops" Shop | Value | Territory Shop1 | 10 | A Shop2 | 5 | B Shop3 | 2 | B Shop4 | -4 | A
Two measures: Measure = MAX('Shops'[Value]) Rank = RANKX(ALLEXCEPT('Shops';Shops[Territory]);'Shops'[Measure])
When you set a filter on territory "A" you get wrong result:
Territory | Shop | Value | Rank A | Shop1 | 10 | 1 A | Shop4 | -4 | 4 - instead of 2 !
For territory B everything is correct
Territory | Shop | Value | Rank B | Shop2 | 5 | 1 B | Shop3 | 2 | 2