- The CALCULATE Function is one of the most
powerful functions in DAX. It's also a very special
function because it's different. Usually functions calculate
from left to right, but the CALCULATE Function
doesn't work like that. The definition of the function itself, can also be quite confusing to understand. So my attempt is to
summarize this definition to a single word, which I'm
pretty sure you'll understand. (upbeat music) Okay, so that single word is pivot table. At least in Excel is considered one word. If you're like, what? - Say what! - Let me explain. First of all, in case you're not familiar with the CALCULATE Function, let me quickly explain what it does. So here I have total
sales per month in 2021, with the CALCULATE Function I
could do something like this, I could get the sales
of innovative products. Now, whether the product
is innovative or not, is not part of my initial filter context I can't see that in the pivot table. That filter is added
directly to the measure. I could go beyond this, and get the sales of innovative products that were bought in bulk. So whether the product
was bought in bulk or not, is also a filter that
was added to the measure. These filters are added
to the filter context that we see in our visual. So here I'm an Excel,
I'm using a pivot table, but the same concept applies if you're using any type
of visual in Power BI. The CALCULATE Function has the
ability to completely change the filter context that's
coming in from your visual. The way these filters are applied, is the same way a pivot
table applies filters. And this concept makes it
so much easier to understand how the CALCULATE Function actually works and why it does what it does. So let's rewind and let's visualize what's happening in the background when we do these types of calculations. Okay, so here I have
total sales per month. First of all, let me show
you quickly to data model, to see how our data is organized. So we're here in the sales table. I have a separate column for
quantity and sales price. To get total sales, I've
used the SUMX Function and multiplied the quantity
column with the sales price. So this is an iterator function. We are going row by row, multiplying quantity with sales price and adding up the results. In the products table here, I
have my product master data, and one of these columns
is the type of product. And that is whether the
product is a standard product, or if it's an innovative product. There is a relationship
between the products table and the sales table. That relationship is
defined by the product code. Now, I wanna create a measure that gets us the total sales
of innovative products. Let's go to Measures, New Measure, call this Sales Innovative Products and start with the CALCULATE Function. So first thing we need is the Expression. This is the expression
that we wanna change based on the additional filters
that we're gonna be adding on top of the initial filters that are coming from the pivot table. The expression that we
wanna change is total sales. The filters that I wanna add, in this case is just one filter
and that's if product type, so I'm just gonna type in Type and I can see the product table here. I'm gonna check whether
this equals innovative. Now, this is not case sensitive so it doesn't matter if I
put a capital I or a small I, it's gonna work either way. Let's close the bracket and adjust the number
formatting to a currency. Now, click on Okay. And I get the total sales
of innovative products. Now, it's difficult to see
what's really happening in the background and how
that additional filter is being added by CALCULATE. So let's do an exercise and
visualize what's happening in a separate pivot table. I'm just gonna copy this initial
pivot table, paste it here, and now remove the measures that we have. You see, the way the CALCULATE
Function works is that, it applies the initial filters that we have in the pivot table
just like any other measure. But then it adds the filters that are inside the CALCULATE Function and last, it calculates the expression. - Whoa! - Sounds a bit confusing, but it's much easier to
remember if we can visualize it. Okay, so first if we start
off with this filter, this is the filter that's
coming in from the pivot table. Next, we have a filter in
the CALCULATE Function. That filter is whether a
product is innovative or not. So let's add that to
our second pivot table that was Type in the product table. And we're gonna filter this
for innovative products. Now, the expression gets calculated. That expression is Total Sales. Take a look at what we have here, 10,972 it's what we see here. Then it's 24,042, what we see here. These are identical,
and this is identical. So this is basically what
the CALCULATE Function does. It adds an invisible pivot
table with its own filters to the initial pivot table
or visual that you see. And then it makes the calculation. And then it puts that
value inside the measure that you've defined here. Now, let's add the second measure. Sales of innovative products
that were bought in bulk. I'll call it Sales
Innovative Bulk Purchase. So let's assume that
we define bulk purchase for any transaction where the
quantity was greater than 10. Now, let's use the CALCULATE
Function to get that. The expression is total sales, so that's the that we wanna change based on these additional
filters that we're gonna add. One filter, was that Type
from the products table equals innovative. The other filters, so I'm
gonna add the next argument, is that quantity was greater than 10. So that's quantity from the
sales table greater than 10, close bracket, let's adjust
the number formatting and add this to our pivot table. So here we have much smaller numbers. Can we visualize that as well? Sure we can. We have an additional
filter that we've added directly to our measure. Let's bring it up so we can see it here. That filter is for quantity, I'm not gonna add quantity
to the values here, bulk quantity to the rows, because quantity here
is acting as a filter. Let's filter that by
going to Label Filters, right, not Value Filters. These are values that you
have inside your pivot table. Quantity is a label in this case, we wanna check whether
this is greater than 10. We click Okay. And now, check what we get. For January, it's the sum of these two that gives us 1,637,
that's what we see here. February, 7,033, April it's the sum of
these two values, 2,523. That's basically what the
CALCULATE Function does. Any filters you define are added to this invisible pivot table, that's then added on top
of the visible pivot table. The expression is then calculated. The final value is put inside
the measure that you define. Okay, so that's my explanation
of the CALCULATE Function. Let me know in the
comments what you think. Now, there is a lot more
that CALCULATE can do, can use it together
with the Filter Function or other table functions to
do more complex calculations, which you can't do with these
standard filter of CALCULATE . Or you can use it to also write your own time intelligence functions. In case you'd like to
Master Power Pivot Dax and Data Modeling, check out my complete
course on xelplus.com. In fact, it's three courses in one, because we create impressive dashboards in the course as well. Many thanks for watching, and I'm gonna see you in the next video. (upbeat music)
