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

[DRay]

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.