Using Percentiles to Bucketize in Beast Mode Query

bdx Member

Hi All,

I have a table with a text dimension (Name) and an associated measure (Score).

I wish to do the following:

  1. Calculate if the Score for each Name is in the top 20th percentile of all entities (highest)
  2. If the Name is inside the 20th percentile, do not update the Name.
  3. If the name is outside of the 20th percentile, change the Name to 'Other'



  • MarkSnodgrass

    You can do this by using Magic ETL. Use the Rank & Window tile and Rank your scores. Add a Group By tile to get a Count of the total rows in your dataset. Join it back to the Rank & Window tile so that the total row count is now a column next to every rank. You can then do the math in the ETL or in a Beast mode to determine the percentile. You can also change the name in the ETL if you want as well so you don't have to do any beast modes.

    Hope this makes sense.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith

    Hi @bdx

    You can do it with a beast mode which will be responsive to any filtering you apply to your card (the percentiles will be recalculated with respect to your filters)

     SUM(SUM(1)) OVER (ORDER BY `Score`) / SUM(SUM(1)) OVER () > .2 THEN ‘Other’ ELSE `Name` END
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx

    while @GrantSmith 's solution will create the buckets, you will still have one row on the axis for each value. it will not aggregate to the bucket level. (in other words you'll see 'other' and 'name' multiple times on the axis instead of having one row "other" and one row "name"

    if that's your desired outcome, you must pre-aggregate the data as Mark has suggested or use DataSet Views to create a similar outcome. Because the data is pre-aggregated and 'bucketized' in ETL or a View, you'll be able to then aggregate on that column.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"