Why CALCULATE() Confuses Almost Every Beginner

When I first started working with power BI, I thought Power BI is the simplest visualization tool that I had ever used. Everything seems to be simple at first, all the process importing, cleaning, visualization using multiple charts, everything was so easy. But gradually things changed, and I realized that I had missed a big part of Power BI. Yes, you guessed it right. DAX.

As far as my understanding goes, functions like SUM (), COUNT (), and AVERAGE () are easy to use, then why does everyone seem to be afraid of DAX? At least I was thought the same

All these beliefs stayed with me until I bult a simple sales dashboard and came across a function called CALCULATE (). At first glance, all the syntax was familiar and no difference from other functions that I have already used.

Then I created a measure using the CALCULATE (), and I noticed something strange.

We will explore my findings through a sample of sales data. The data set consists of product categories and sales values.

Lesson no 1: CALCULATE () IS NOT NEEDED FOR EVERY CALCULATION

I have learned a new function so, I started using to everywhere. I have created Total sales by using CALCULATE ()

Then I have created a simple measure, total sales amount by using SUM (). The value of total sales amount was 100,000, the result was the same what I expected.

After this, I have realized that CALCULATE () is not needed for every measure. Because I thought CALCULATE () makes every measure powerful but it didn’t.

The first measure calculation is not wrong, but it didn’t add any value to the calculation. So it is only used if we want to add, remove, or modify the filters. If no change is required, a simple aggregation function is more than enough.

Total sales amount using CALCULATE().

Total sales amount using the simple aggregate function.

Lesson no 2: CALCULATE() ADD FILTERS

I wanted to find sales of the Electronics category, for this I came across CALCULATE (). The syntax is simple; I have created another measure to find the sales amount of Electronics.

What is the confusion here? I just filtered the Electronics. I think the confusion starts after this.

Lesson no 3: CALCULATE() REMOVE FILTERS

I put all these categories into a table. All the categories showed the correct sales amount.

Instead of showing category sales, I wanted to display the total company sales in spite of the category.

I have added ALL () here with CALCULATE ().

You got confused here, right?

Why CALCULATE () + ALL (), ignoring the category that exists in the table visual?

Here, the perspective is changed when we place a category in a visual, Power BI automatically filters the data for each row, so that electronics see only electronics data. Furniture sees only furniture data.

When you apply the ALL () function, it removes that category filter. So, CALCULATE () will not filter a single category; it will come up with the entire sales table again. A small difference completely changed the entire result.

Lesson no 4: CALCULATE() CAN OVERWRITE THE EXISTING FILTERS

So, I thought like I had finally understood the CALCULATE (). This function can add filters and remove filters, after this I tried a different experiment with CALCULATE (). I have created a measure for west sales.

My intention behind this creation was simple: I wanted to check the sales of the West region only. For that, I have added a region to the table and the newly created measure too.

What I expected from the table was east row show the East sales, the West row shows the West sales, South row shows the South sales. But I didn’t get the expected result instead, the table displayed the same West sales value for each row. East showed West sales, South showed West sales, and the West showed West sales. I have got confused here because, as a beginner, it was difficult to understand the behaviour of the CALCULATE ().

What happened here is that the table has already filtered each row by region. For example, the East row was looking for east sales, likewise the West row for West sales data. But we have asked for CALCULATE () to add only the West region sales data.

So now Power BI has two instructions, one is a ,visual filter and the other one is CALCULATE () filter that is to look into the West Region Sales. Power BI followed the second filter instruction inside the CALCULATE (). As a result, each and every row showed West sales even if the row show shows Some other region, it showed only West region sales data.

What we can learn from this is CALCULATE () can also replace an existing filter with new one.

Conclusion:

The reason behind every newly learning Power BI aspirant got confused of CALCULATE () is not because of the syntax is difficult. The real hidden problem is how Power BI thinks about data.

Through my entire journey of DAX learning, what I understood is that CALCULATE () is actually one of the most useful functions in Power BI.

Once you understand the behaviour of Power BI, how the filters affect the calculation, why Power BI is giving that particular result, CALCULATE () is just a cake walk for beginners.