The Power of CALCULATE in Power BI

Last week in my blog and video I discussed how Power BI could have made my life as a teacher much easier.  It is not that Power BI can do things that I could not on my own.  It is the fact that Power BI could have saved me so much time.  This led me to some thoughts and questions.  

• How could I have impacted the students more in the classroom with this extra time? 
• Would teachers who were not analyzing student data due to time constraints now start? 
• How can I make the data tell my students a story that gives them an accurate picture of their progress?

 That last question is what leads to this week’s blog.  

Many times the metrics (which is just a fancy word for "numbers") of what we want to show are easy to do by hand with a basic math background.  Although easy, it can be very time consuming when dealing with different groups of data: like 6 periods of students instead of 1 period. When we want to do these calculations quickly with minimal effort in Power BI it is not as straightforward as a process as if just doing them by hand.  

For example, what if I want each one of my 130 students to see his or her grade compared to the first period's class average? Typically, I would have all the grades stored in a column so I cannot use a simple "measure".  A measure will look at one column and perform an operation over the entire column. For example, if the numbers 8, 9, 10, and 11 were stored in a column and we programmed our measure for the sum we would get 38.  But what if, I just wanted the sum of the first two numbers in the column.  This is where it is easy to do by hand, but a little harder to do with the coding in Power BI.  Let me explain further.

A basic measure in Power BI will always be filtered down in a report visual (table, matrix, chart, etc.) based on what is called a row-level context.  So, if in a table I only had one row that contained the formula for finding the average of all the grades stored in a column show up in a table I would see the average of all my students.  The minute I add student names to the table, I will now have more than just one row of data.  I will have one row for each student’s name. I will also see the unique average of each student next to their name because each row average is now filtered down based on the student name.  The previous number of the entire average for all students has now disappeared.  Usually, that’s exactly what we want.  What if I wanted my students to be able to see their average grade right next to the period average grade for that assignment?  What if I wanted my student to see their average grade compared to all of the students I teach who took the same assignment?  That’s where the function CALCULATE comes into the story.  

CALCULATE is a DAX function that allows you to take a basic measure calculation and modify the row-level context. Woah!  CALCULATE is super powerful  For my prior example, I can override the student filtering the average calculation and I can have the calculation filtered by something that isn’t in the row,  a select few items in the row, or ignore/use the outside slicer filters.  Confused yet?  If not you are probably a master of Power BI and DAX already.  If you are confused, then welcome to my world when I first started learning Power BI.  I have a very strong mathematics background, but when having to apply my understanding of mathematics to telling a computer how to do my calculation without just saying it in normal words took me a while to wrap my head around.

So, please follow along with me in this video as I attempt to help you understand how the function CALCULATE works along with the help of the other DAX functions ALL, ALLSELECTED, and ALLEXCEPT.  This demo will for sure shed light on education data to help a classroom teacher make their data come alive.  If you are not a classroom teacher this demo is just as useful as you can apply it to financial, medical, and sports data just to name a few.