# Filter based on count for two categories

Hi everyone,

I’m trying to figure out how to add some logic to a measure that will filter it based on the count for two categories - student name and report cycle name.

For any given report cycle, a pupil should have 4 report grades. A sum of these report grades for each pupil is then used to calculate the average for each report cycle (the average of the sum of grades for each pupil).

However, if a pupil does not have 4 report grades, I don’t want them to be included in the measure.

How can I get around this problem?

Thanks,
Matt AverageX help.pbix (48.7 KB)

Hi @corkemp

Try this

AverageX help.pbix (57.2 KB)

4 Likes

Hi @corkemp, did the response provided by @Rajesh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Thank you! This works perfectly!

How could I now add the logic to the expression as shown in my original post (a pupil must have at least x number of report grades to be included in the calculation)? I’ve tried this:

``````SUM/AVG =

VAR Tab = SUMMARIZE('Report data','Report data'[Name],'Report data'[Year group],'Report data'[Report cycle name],"Total",SUM('Report data'[Attainment grade]), "Count", COUNT('Report data'[Attainment grade]))

RETURN

CALCULATE(IF( AND(HASONEFILTER('Report data'[Name]),HASONEFILTER('Report data'[Report cycle name])),SUM('Report data'[Attainment grade]),AVERAGEX(Tab,[Total])),FILTER(Tab,[Count]=3))
``````

But it isn’t working, I’m guessing because it’s coming from the Tab virtual table?

Thanks for the help
Matt

I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

@corkemp

We can filter virtual table also, but sample data has Count 2 only.

Hi Rajesh,

With the count only being 2, I was expecting my count = 3 filter to not return any results, but nothing changed.

This is only dummy data, in reality the expected count for each pupil is 6, but I’d like to understand the method to apply in other circumstances as well.

Thanks for helping.
Matt

Hi @corkemp

Try this measure. Added dummy data, attached PBIX file also.

AverageX help.pbix (58.0 KB)

Hope this helps

2 Likes

Fantastic - it works perfectly. Thank you.

Matt