Forum Discussion

liamcameron's avatar
liamcameron
Cloud Apps
09-26-2024

Filter out groupings with less than a certain value

Similar to a sports KPI... i want 3 point shooting percentage, but only for athletes that have taken a minimum 50 shots

i have a kpi that is AVG([PersonId],[# of completionId]) where completionId is how many things they've done

 

I want to aggregate this into averages for the organization, but i need to exclude Persons who had less than 50 records.... so bassically  a filter that says exclude Id's from this result where the count of their completionId is less than 50....

i can get this to work SORTOF by doing a top{x} and saying give me the top 100 by Completion#, but i want this to basically just remove any PersonIds with less than 50

Dashboards & Reporting

2 Replies

  • harikm007's avatar
    harikm007
    Data Warehouse
    09-27-2024

    Hi liamcameron ,

    One option is to use a CASE statement instead of '[# of completionId]'. So the formula will be:

    avg([PersonId], case when[# of completionId] > 50 then[# of completionId] else null end)

     

    -Hari

  • DRay's avatar
    DRay
    Admin
    10-10-2024

    Hello liamcameron,

    I’m following up to see if the solution offered by harikm007 worked for you.

    If so, please click the 'Accept as Solution' button on the appropriate post, that way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

    Thank you.