Forum Discussion

lukeSDM's avatar
lukeSDM
Helper V
5 years ago
Solved

Measure not counting properly

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.
Need Help
  • Icey's avatar
    Icey
    5 years ago

    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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      lukeSDM
      Helper V
      5 years ago

      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's avatar
        amitchandak
        Super User
        5 years ago

        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.

         

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    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.