Hi, first post 🙂 and new to Pivot's
The attachment is a sample of student results and is structed by academic year, quarter, subject and grade. At this level, (year / qtr / grade) we need to identify two %'s and then present this data onto bar chart.
1. Identify what % of students scored >=50%
2. Identify what % of students scored >=60%
I have subscription to Excel 365
Currently this is achived using long formulas and a series of if statements, so having recently been introduced to pivot tables i wonder if it's possible to achieved this?
Any advice appreciated.
J
Hi James,
Welcome to our forum!
You would have to use a Power Pivot PivotTable and write a DAX measure to count scores > a certain percentage. In the file attached I've provided both a Power Pivot and formula solution. Hope that helps.
If you'd like to learn Power Pivot, please consider my Power Pivot and DAX course.
Mynda
Hi Mynda, (apologies if i should have created a new post and happy to do so if needed)
Using the logic you provided with a Formula solution, we have been able to successfully analyse our results on a quarterly basis, thank you.
=COUNTIFS(Table5[Academic Year],B27,Table5[Grade],E27,Table5[Result],$F$4,Table5[Subject],$F$3)
Our junior school would now like to further analyse the student results but over the whole academic year. This will require removing the quarter parameter from the formula, but include an Average calculation of the 4 quarters to see if that is >50%.
Are you able to help me on the new formula which, using the same example attachment?
Many thanks
James
Hi James,
Glad we could help. Yes, please start a new post with this question. I will be away for the rest of the week, so don't address it to me specifically so that others helping out on the forum can help you.
Mynda
