Identify percentile within a grouping

neilprobst
neilprobst Member
in Magic ETL

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?

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • neilprobst
    neilprobst Member

    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
    verytiredgirl Member

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

  • neilprobst
    neilprobst Member

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

    image.png

    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

    image.png

Categories