Log In
Register
Home
Dashboards
Power Query
Power Pivot
Power BI
February 6, 2022
Offline
Hi, i'm using MS Excel 365.
Earlier this year Mynda kindly shared with me how to calculate what % of students scored >x% in the quartley exam results. We compare the results against two measures - Success Rate (SR) and Proficiency Rates (PR). It was achieved using a formula which referenced the measures (>x%) on the dashboard.
=SUM(COUNTIFS
(dt.StudentResults[Academic_Year],A9,dt.StudentResults[Quarter],B9,dt.StudentResults[Grade],C9,dt.StudentResults[Subject],D9,dt.StudentResults[Attribute],H9,dt.StudentResults[Value],'SR-PR Dashboard'!$O$2)
/
COUNTIFS(dt.StudentResults[Academic_Year],A9,dt.StudentResults[Quarter],B9,dt.StudentResults[Grade],C9,dt.StudentResults[Subject],D9,dt.StudentResults[Attribute],H9))
The school would now like to see same results but over multiple quarters:
- The results for Semester 1 - By student, sum of Q1 and Q2 results divide by 2, then calculate % that are > x%
- The results for Semester 2 - By student, sum of Q3 and Q4 results divide by 2, then calculate % that are > x%
- The results for full academic year - By student the sum of Q1, Q2, Q3, Q4 divide by 4, then calculate % that are > x%
Attached is my workbook, containing the worksheets
- SR-PR Dashboard - this has the x% value
- SR-PR Workings - contains the formula in cells E9 to E14 and F9 to F14.
- dt.StudentResults - data set
Any help would be greatly appreciated.
James
See Answer
November 8, 2013
Offline
Hi James,
Add a Semester column in Student Results table with this formula:
=IF(OR([@Quarter]="Q1",[@Quarter]="Q2"), 1,2)
Then, instead of referring to quarters in COUNTIFS(dt.StudentResults[Quarter],B9....), use Countifs(dt.StudentResults[Semester],1,.....) to refer to semester 1 (or 2).
For Full year, removing Quarter or Semester criteria from formulas should work.
Answers Post
1 Guest(s)
