Create a DAX measure that counts summarized hours to date compared to a target

[dkernen2]

I have a list of students who are submitting internship hours.  I need a measure that calculates their hours-to-date spent compared to the hours-to-date target.  I have been able to create a metric that shows the ratio, but I am struggling creating a metric that counts the number of students that are on/below/above target to date.  I've tried SUMMARIZECOLUMNS but that isn't meeting my needs.  

I have, by student

Coach	Student	Hours to Date	Target to Date	Ratio
AA	StudentA	105	75	140%
BB	StudentB	89	75	119%
BB	StudentC	61	75	81%

What I need is to create measures that calculate counts by coach

Coach	Students	Hours to Date	Target to Date	Students Behind Target	Students At or Above Target
AA	1	105	75	0	1
BB	2	150	150	1	1

Any help you can provide would be most appreciated.  Sample file is here.

[dkernen2 · Accepted solution]

I am thinking this should work:

Behind Target = COUNTROWS(FILTER(VALUES(Attendance[Student ID]),[Total to Target To Date]<1))

[v-heq-msft]

Hi @dkernen2 ,

1.Create two measures to calculate diffierernt types

Students At or Above Target =
VAR Students =
    COUNTROWS ( VALUES ( 'Table'[Student] ) )
VAR AtOrAboveTarget =
    COUNTROWS (
        FILTER ( 'Table', 'Table'[Hours to Date] >= 'Table'[Target to Date] )
    )
RETURN
    AtOrAboveTarget

Students Behind Target =
VAR Students =
    COUNTROWS ( VALUES ( 'Table'[Student] ) )
RETURN
    Students - [Students At or Above Target]

2.Create calculate table

Coach =
SUMMARIZE (
    'Table',
    'Table'[Coach],
    "Students", COUNTROWS ( VALUES ( 'Table'[Student] ) ),
    "Hours to Date", SUM ( 'Table'[Hours to Date] ),
    "Target to Date", SUM ( 'Table'[Target to Date] ),
    "Students Behind Target", [Students Behind Target],
    "Students At or Above Target", [Students At or Above Target]
)

3.Final output

Best regards

Albert He

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

[dkernen2]

Thank you Albert.  Your solution requires a pre-aggregated table of hours to date, which I do not have.  My attendance table has daily rows.  I need to summarize the hours to date by student with the original table that has multiple attendance rows per student.  Any thoughts?