summarize percentile function not working as expected

Question

I am attempting to group customer sales by category using percentile to get a normal distribution. I cannot use a std dev because of the nature of the data the cluster toward the low end and the high variance would have -1 std dev from the median below 0. So I settled on running it through a percentile summarize and taking the 2.5, 13.5, 34, 34, 13.5, 2.5 as the thresholds for my groups to get an even distribution. But when i perform the percentile function it returns the raw value of sales, not a percentile ranking. has anyone ran into this. The percentile macro does not work for me because i believe it is std dev based and in some uncommon categories my lowest value will be 34 and highest will be 96. i attached a sample data set of what my expected/desired result is vs what the tool is spitting out.I just made up some data to keep it simple and for privacy reasons

StudentClassrawscorePercentile_rawscoreexpected result azmodiushistory1501505050thbrianhistory858500thbritneyhistory1841848080thcaseyhistory1551556060thhelgahistory86861010thjaimehistory96962020thjameshistory1251253030thjennyhistory1301304040thkellyhistory1651657070thkristenhistory1881889090thazmodiusmath4214219090thbrianmath1581581010thbritneymath2552556060thcaseymath2852857070thhelgamath2482484040thjaimemath2352353030thjamesmath2542545050thjennymath2882888080thkellymath14514500thkristenmath2102102020thazmodiuspe14514500thbrianpe2582584040thbritneype2312311010thcaseype2472472020thhelgape9559559090thjaimepe7527528080thjamespe2512513030thjennype2652655050thkellype5965967070thkristenpe4564566060th

patrick_digan · Accepted Answer

@BFritter How about the attached.

I used the sort tool to order the records by score. Then the multi-row formula ranks them within each class. Then the summarize gets the max rank per class. The find replace then adds the Max rank back to each class. The formula tool gets the percentile and multiplies it by 100.

Hopefully that's the direction you were looking to go!