Filter out groupings with less than a certain value

[liamcameron]

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

[harikm007]

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