Skip to main content

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.



Popular posts from this blog

The Teacher Becomes the Student

First, let me tell you that this won’t be your typical Power BI or tech blog that you usually see.

Let me set the scene.

It was a year and a half ago when I got lucky enough to be introduced to Brian Knight, the CEO of Pragmatic Works over lunch. He had heard from a mutual friend that I was looking to make a career move after teaching Algebra for the past 15 years. Brian started telling me about the company and all the different facets it encompasses. From training, sales, consulting, and app development.

Mind you at this time I had never heard of Microsoft Power BI, SQL Server, Microsoft Azure, or have any experience with coding. He told me an opportunity at his company may present itself in the future where I could transfer over my teaching skills into the training side of the above-mentioned programs.

I immediately got to work with their On-Demand Learning system to learn as much as possible while still teaching full time, private tutoring 9 hours a week, chasing around a 1 and ha…

An Update on My Journey from Algebra Teacher to Power BI

Time for an update on my journey. We last left off in March with the following words: “I am still nowhere near mastering this program.  As an educator I know that repetition, studying, taking detailed notes, and not being afraid to ask for help is what turns a student into being a success. I plan on doing all of those things on my journey to learning Power BI and the other programs I will be tasked with teaching.
I wanted to share my process of transitioning from an Algebra teacher to a trainer with you in hopes that if there is anyone else out there who is apprehensive about the new technology you are learning for your job or you are considering making a change in your career,  or if you are just feeling overwhelmed with whatever the task may be - you can stop by from time to time for some motivation and quick tips."
March has turned into May and I have taken myriad courses covering Power BI. 
Did I retain every single detail and master the program? No, of course not. The Power BI program is …