cancel
Showing results for 
Search instead for 
Did you mean: 

Filter out groupings with less than a certain value

liamcameron
8 - Cloud Apps
8 - Cloud Apps

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

2 REPLIES 2

harikm007
13 - Data Warehouse
13 - Data Warehouse

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
Community Team Member
Community Team Member

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.

David Raynor (DRay)