RANKX jumping from 208 to 4496 when it should be going 208 to 262

[king4646]

Hi, I have Pivot table that isolates my customers by the types of products they consume

so my table rows are product type, customer name, customer URN in that order. My value is points scored based on a measure named [percentile pts]

I want to rank my customers in their product types, my measure seems to be working until it gets to certain numbers. For example product type 1 gets to 208, when it should change to 262 it doesn't it turns to 4496

product type 2 gets to 168 then when it should go to 179 it jumps into the thousands too

My current DAX is

=RANKX(ALLSELECTED('data'[customer]),

Calculate([percentile pts],

ALL('data'[customerURN]))

I am on excel office365

[Mrxiang]

Based on the information you provided, it seems like the issue might be with the calculation of your measure [percentile pts]. It's possible that the calculation is not taking into account the product type and is instead aggregating all customers together.

To fix this, you can try modifying your DAX formula to include the product type in the calculation of your measure. For example, you can use the CALCULATE function to filter the data by product type before calculating the percentile points:

=RANKX(ALLSELECTED('data'[customer]),
CALCULATE([percentile pts],
FILTER('data', 'data'[product type] = EARLIER('data'[product type])),
ALL('data'[customerURN])))

This should ensure that the percentile points are calculated separately for each product type, and should hopefully resolve the issue with the rankings jumping to high numbers.

[king4646]

Morning unfortunately it is saying "a single value column for 'product type' it directs me to that EARLIER section of the code.

The percentile points measure is a sum of points assigned based on revenue, subscription revenue, length of time as customer for example if theyre in the 90th percentile for revenue they get 6 points but if theyre low down on subscription revenue they get 1 point so they would total 7 points. There are 6 individual points being fed into this measure.

for context this analysis is only 1045 customers across 7 product categories, 352 of them are in the DIG category

[king4646]

To add to this, 2 of the points measures ranged from -6 to +6, if there revenue has decreased year on year or qtr on qtr by 90% or more they would score -6 points when I take those 2 measures out of the [percentile pts]

Investigating the issue it seems as though when the total score of becomes 0 or a minus figure it makes the rankings wobble even for some that have a positive score