Log In
Register
Home
Dashboards
Power Query
Power Pivot
Power BI
February 6, 2022
Offline
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
See Answer
July 16, 2010
Offline
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
Answers Post
Dashboards
Power Query
Power Pivot
Power BI
February 6, 2022
Offline
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
July 16, 2010
Offline
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
1 Guest(s)
