Identify percentile within a grouping

neilprobst

Hello all,

I have a dataset with 3 columns: Parent Company, Child Company, and Total Sales. I was asked to identify the bottom 10% by rank of the child companies for each parent company and calculate the average total sales at that percentile.

This discussion gives some insight on how to start. I used Rank and Window to get the rank of child companies for each parent, but where I am stuck is how to identify the bottom 10th percentile (or any percentile). Would I need to do this with a Beast Mode at the card level?

GrantSmith

Once you have the rankings you'll need to do another window function to get the maximum rank for each partition / parent company. Then you can divide the rank by the max rank to get the rank percentage / percentile.

GrantSmith

Once you have the rankings you'll need to do another window function to get the maximum rank for each partition / parent company. Then you can divide the rank by the max rank to get the rank percentage / percentile.

neilprobst

Thanks, this solves my problem (though I used a Group By rather than a second Window function to get the max rank). I think my issue was more my own innumeracy with percentile calculations rather than the ETL process itself. Thanks for talking me though it

verytiredgirl

@neilprobst how did you do the group by the get the max rank?

neilprobst

@verytiredgirl sorry for the late reply. I used the 'Maximum' aggregation

In this example, "GDV Rank" is the field that contains the ranked values for each partition. This Group By transform gets the max value for each partition. I then merge this back with the main dataset so each row has a rank and a max rank that will allow for a percentile calculation