cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
laura_ESNH
Frequent Visitor

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

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.

laura_ESNH_0-1618412211597.png

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:

laura_ESNH_1-1618412211601.png

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.

laura_ESNH_2-1618412211604.png

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.

 

1 REPLY 1
lbendlin
Super User III
Super User III

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.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors