How do I calculate % of students making progress on goals that are categorized

[laura_ESNH]

I am managing a program where we are tracking student progress on goals and want to know the % of students that are making progress. They are scored daily on each goal. I have a SQL table that lists each student, their goals, the category associated with the goal and the monthly average score.

My first step was to determine if a student is making progress on a specific goal.

I did this by creating calculated measures for their initial goal score:

Initial Goal Score = CALCULATE(MAX('Outcome YTSGoalNeedTracking'[AverageScore]), FILTER(ALL('Outcome YTSGoalNeedTracking'[DateServiceProvidedFirst].[Date]), 'Outcome YTSGoalNeedTracking'[DateServiceProvidedFirst].[Date] = MIN('Outcome YTSGoalNeedTracking'[DateServiceProvidedFirst])))

Their latest goal score:

Final Goal Score = CALCULATE(MAX('Outcome YTSGoalNeedTracking'[AverageScore]), FILTER(ALL('Outcome YTSGoalNeedTracking'[DateServiceProvidedFirst].[Date]), 'Outcome YTSGoalNeedTracking'[DateServiceProvidedFirst].[Date] = MAX('Outcome YTSGoalNeedTracking'[DateServiceProvidedFirst])))

I then created two additional calculated measures with IF statements to identify students that have made progress, one for a Yes/No that displays in the table above and the other with a 1/0 for counting.

Goal Progress Made (Y/N) = IF('Outcome YTSGoalNeedTracking'[Final Goal Score] > 'Outcome YTSGoalNeedTracking'[Initial Goal Score], "Yes", "No")

Goal Progress Made (1/0) = IF('Outcome YTSGoalNeedTracking'[Final Goal Score] > 'Outcome YTSGoalNeedTracking'[Initial Goal Score], VALUE("1"), VALUE("0"))

I have been able to successfully calculate the % of students making progress for each goal as displays in the table below:

I did this by creating a calculated measure to count the number of students assessed on a goal
No. Students Assessed = CALCULATE(DISTINCTCOUNT('Outcome YTSGoalNeedTracking'[ID]), 'Outcome YTSGoalNeedTracking'[AverageScore] > 0)

In order to get a sum of the total students that made progress, I created a table of the student IDs  -- Student List = VALUES('Outcome YTSGoalNeedTracking'[ID] -- and created a relationship between the two tables with the ID.  I then used SUMX to get a total of the students that made progress. 

Goal Progress Made Sum = SUMX(VALUES('Student List'[ID]), 'Outcome YTSGoalNeedTracking'[Goal Progress Made (1/0)])

After that, the % was a simple calculated measure: 

% Students Progress = 'Outcome YTSGoalNeedTracking'[Goal Progress Made Sum] / 'Outcome YTSGoalNeedTracking'[No. Students Assessed]

NOW FOR THE PROBLEM: Each of the three goals listed above are part of the “Employment” category. When I create a visual that used the category instead of the goal, the no of students assessed and the no of students with progress is not accurate.

It should be 60 students assessed, 18 with progress and 30% progress.

I’m thinking that I may need to use SUMX again. I tried building a table with the Categories (Mission Categories = VALUES('Outcome YTSGoalNeedTracking'[Category]) and then used SUMX to get a total of the students assessed in the category with:

Category No. Assessed = SUMX(VALUES('Mission Categories'[Category]), 'Outcome YTSGoalNeedTracking'[No. Students Assessed])

But the resulted in the same number of 49. I think the issues lies with the fact that some students have two goals within the same category (see ID000483 in the first image) and that the count on the number of students assessed is a distinct count on the ID -- DISTINCTCOUNT('Outcome YTSGoalNeedTracking'[ID]). I just don't know how to adjust the measures to account for this issue.

Any help would be GREATLY appreciated. I have a number of other programs that I need to do the same type of calculation.

[lbendlin]

Rethink your use of FILTER(ALL()) in this scenario.  FILTER() is not really required since you are already inside a CALCULATE(), and ALL() removes all existing filters which is most likely not what you want.  Consider using ALLEXCEPT() or similar instead.