Select Page

Using CALCULATE in DAX (Intermediate)

by | Jan 25, 2021 | Video

When using CALCULATE in DAX, you need to always make sure that correct filters are applied. In other words, CALCULATE you can override the existing filter context with the new ones inside the function. Watch our short tutorial above.

Basic calculation:

First, let’s start with the use of CALCULATE to find out the percentage of sales per each brand. The basic way is to create a measure that will ignore the filters and show all sales for each brand:

  • All Brand Sales = CALCULATE ([Total Sales], ALL (‘Product’ [BrandName]) )

Then we could divide the sales per each brand by all sales to find out percentage.

  • % of Sales = DIVIDE ([Total Sales], [All Brand Sales])

As a result, we get the percentage of sales for each brand category.

Advanced calculation:

A more advanced way to calculate the percentage of sales is by using variables. That way, we can skip creating extra supporting measures:

  • % of Sales = 
    VAR CurrentBrandSales = [Total Sales]
    VAR AllBrandSales = CALCULATE [ Total Sales], ALL (‘Product’ [BrandName]))
    VAR Ratio = DIVIDE (CurrentBrandSales, AllBrandSales)
    Return Ratio

By the way, to learn more about the use of FILTER and ALL functions, click here.

The above measure works like magic. However, there is a caveat. If you add another category to the table, such as ContinentName, the numbers get messed up. This happens because in the % of Sales measure, we put AllBrandSales as an explicit filter in the denominator. When we put the second category in the table, our filters changed and now include the ContinentName. That is why we need to modify our code and add the ContinentName in the denominator part. As a result we get the following:

  • % of Sales = 
    VAR CurrentBrandSales = [Total Sales]
    VAR AllBrandSales = CALCULATE [ Total Sales], ALL (‘Product’ [BrandName]), ALL(‘Geography’ [ContinentName])
    VAR Ratio = DIVIDE (CurrentBrandSales, AllBrandSales)
    Return Ratio

Now, after we fixed the filter issue, our numbers are correct. When using CALCULATE it’s all about making sure that correct filters are applied.

To learn the fundamentals of the CALCULATE function, click here.

Tags:

DAX | Power BI | video
Share This