Power BI Calculate

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Power BI Calculate Function

CALCULATE function is the often used DAX function in Power BI. Even though CALCULATE function cannot do anything, it works as a base function to apply other DAX functions in different scenarios. For example, suppose you want to apply a filter and find the average sales for one particular city. In that case, we can use the CALCULATE function to apply the filter and arrive at calculations.

Table of contents

So, the CALCULATE function evaluates the expression given by the user with all the applied filters. Below is the syntax of the CALCULATE function.

Power bi Calculate Syntax
  • Expression: This is nothing but what is the expression we need to perform. For example, if we need to get the sales total.
  • Filter 1: Based on the Expression given, what is the filter we need to apply. For example, to get the Expression result, Filter 1 will be anyone particular city.
  • Filter 2: Based on the Expression given, what is the second set of filters, we need to apply, for example, in the particular city particular PIN Code region.

This article will take you through one of the important and often used DAX functions: CALCULATE in Power BI.

Power-BI-Calculate

Examples of Dax Calculate Function in Power BI

Below are examples of the DAX CALCULATE function.

Example #1

Below is the data we will use to demonstrate the CALCULATE function in Power BI. You can use the same data by downloading the Excel workbook from the below link.

Power Bi calculate (Excel data)

You can directly upload the data table to the Power BI file. We have already uploaded the table to the Power BI Desktop file.

Power Bi calculate (uploaded data)

Now, we will experiment CALCULATE function to arrive at different sets of results.

Arrive at one particular city sales total

For example, assume you need to create a "New Measure," which gives one particular city total, for example, "Columbia" city.

The steps to use the DAX calculate function in Power BI is as follows.

  1. Right-click on the table, and choose the "New measure" option.


    Power Bi calculate (new measure).png

  2. Give the name to this measure “Columbia City Sales.”


    Power Bi calculate (columbia city)

  3. Now, open the CALCULATE function.


    Power Bi calculate (Calculate option)

  4. An expression is the first option. We need to add the "Columbia" city total in this example, so open the SUM function.


    Power Bi calculate (Sum)

  5. The "ColumnName" we need to SUM is the "Sales Value" column, so choose the respective column.


    Power Bi calculate (Sales value)

  6. The "ColumnName" we need to SUM is the "Sales Value" column, so choose the respective column.


    Power Bi calculate (Filter)

  7. The table we refer to is the "Sales_Table." So first, choose the table name.


    Power Bi calculate (Sales Table)

  8. We need to select the "City" column for FilterExpression and give the criteria as "Columbia."


    Power Bi calculate (Columbia).png

  9. We are done. Close two brackets and press the "Enter" key to get the new measure.


    Power Bi calculate (new measure ans)

  10. Drag the "Columbia City Sales" to "Fields" to see the new measure.


    Power Bi calculate (drag columbia field)

  11. Now, this measure only gives the total sales of the city "Columbia."


    Power Bi calculate (Columbia card)

  12. You can cross-check the “Columbia” city total in Excel as well.


    Power Bi calculate (Pivot table)

Like this, we can use the CALCULATE function to arrive at different results.

Example #2

Assume for the city "Columbia," we need only the sales value for the state "South Carolina," so we need to apply two filters this time.

  • With the continuation of the previous DAX function, close only one bracket and open another filter function.
Power Bi calculate (Another Filter)
  • Once again, mention the table we are referring to.
Power Bi calculate (Filter sales table)
  • This time we need to apply the filter for the column "State," select the column and give the criteria as "South Carolina."
South Carolina
  • The "Sales Value" of the state South Carolina is shown below.
Power Bi calculate (South Carolina Result)

Our new total will be 15,099. It is the sales value for the state "South Carolina" in the city "Columbia."

Power Bi calculate (New Total)

Example #3

You want to find the percentage share of each city for the overall sales. It is done by using the below formula.

% Share = City Sale / Overall Sales * 100

But one problem is this is not the excel to use flexibly with cell references. The idea is to get the overall sales total against all the city totals.

  • So, we need to create one more measure, which is as follows.
Overall sales
  • Now, insert the "Table" visual first.
Table
  • Add the "City" name and "Sales Value" columns for this table visual.
Table Fields
  • As you can see above, we have each city's total here. The overall sales value is 79,393. Now, drag and drop the new measure column "Overall Sales."
Drag Overall sales
overall sales 1
  • Now, as you can see, against each city, we have an "Overall Sales" value. Now, using these two measures, we can create a new measure to get the percentage share. Use the below function to get the new measure.
Percent share Formula
  • Drop and drag this new measure to the table to get each city's "% share."
% share result

Now, we have a "% share" column. Like this, using the CALCULATE DAX function, we can arrive at expressions based on different filters and conditions.

Note: We can also download the Power BI CALCULATE function file from the link below. We can view the final output.

Things to Remember

  • The CALCULATE function in Power BI is used to arrive at different results based on conditions.
  • The CALCULATE function is used with other DAX functions in Power BI to get the job done.