Measure not counting properly

[lukeSDM]

I have 3 measures:

The first one calculates a students attendance(This one works fine it counts specfic marks which are date stamped and divides by total) -

Calculated attendance = ([ALL Present]/[Measure])*100

The second one then looks at the first measure and gives them a Y/N if they are below 96% -

pa y/n = IF([New_Calculated] <96, "Y", "N")

The third is meant to count those who have a Y from the previous measure -

<96 = (COUNTROWS(FILTER('Pupil_Data','AttendanceOvertime'[pa y/n]="Y"))).

My last measure does not work though, it does not count all students for a specific week with a Y.

 As you can see in Week 30 in the screenshot there is more than 5 students with a Y but Power Bi has calculated there is just 5 and this is clearly wrong.

There is a relationship between the attendance sheet and Pupil data sheet. The measure has to refer to a single student which is the pupil data sheet as the attendance sheet has multiple entries for each student, unless someone can tell me another way to do this?

Can anyone advise me what I am doing wrong?

Unfortuantley I cannot provide example data due to GDPR.

[Icey · Accepted solution]

Hi @lukeSDM ,

---

The third is meant to count those who have a Y from the previous measure -

<96 = (COUNTROWS(FILTER('Pupil_Data','AttendanceOvertime'[pa y/n]="Y"))).

---

Try to count 'AttendanceOvertime' table, not 'Pupil_Data' table like these:

<96 =
COUNTROWS ( FILTER ( 'AttendanceOvertime', [pa y/n] = "Y" ) )

<96 =
CALCULATE (
    DISTINCTCOUNT ( 'AttendanceOvertime'[Student] ),
    FILTER ( 'AttendanceOvertime', [pa y/n] = "Y" )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@lukeSDM , I am not able see the group by/un summarized column of visual.

based on that try

COUNTROWS(FILTER(values('Pupil_Data','AttendanceOvertime'[Group by visual]) , [pa y/n]="Y"))

replace [Group by visual] with correct column

[lukeSDM]

@amitchandak 

There is no columns they are all measures.

You can see the total in the table of the <96 measure is 5 which is incorrect as in the p/a y/n measure in the table shows more than 5 Y's.

This is how it looks in graph from and all weeks are wrong.

Many thanks,

[amitchandak]

@lukeSDM , what you are using on axis ? That is your group by

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[lukeSDM]

My attendance table has a relationship with a date table so I can timestamp students attendance during specific weeks.

[lukeSDM]

@amitchandak 

My 'X' Axis is the academic week number.

My 'Y' Axis is the count of Y.