Filter based on count for two categories

DAX DAX Calculations
1

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?

Output required
Output required1201×741 27.1 KB

Thanks,
Matt AverageX help.pbix (48.7 KB)

2

Hi @corkemp

Try this

image
image1343×627 44.2 KB

image
image1363×781 96.8 KB

AverageX help.pbix (57.2 KB)

4 Likes
3

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!

4

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 :smiley:
Matt

5

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!

6

@corkemp

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

image
image867×730 65.3 KB

7

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

8

Hi @corkemp

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

image
image1520×742 54.1 KB

image
image870×727 64.6 KB

AverageX help.pbix (58.0 KB)

Hope this helps

2 Likes
9

Fantastic - it works perfectly. Thank you.

Matt