Power BI Calculate Function
CALCULATE is the often used DAX function in Power BI, even though CALCULATE cannot do anything, this function works as a base function to apply other DAX functions in different scenarios. For example, if you want to apply filter and find the average sales for one particular city then we can use the CALCULATE function to apply filter and arrive calculations.
So, the CALCULATE function evaluates the expression given by the user with all the applied filters. Below is the syntax of the CALCULATE function.
- 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 filter we need to apply. For example in the particular city particular PIN Code region.
In this article, we will take you through one of the important and often used DAX functions Calculate in Power BI.
Examples of Calculate Function in Power BI
Below are the examples of Calculate function in Power BI.
Below is the data we are going to use to demonstrate the CALCULATE function in Power BI. You can use the same data by downloading the excel workbook from the below link.
You can directly upload the data table to the Power BI file, I have already uploaded the table to Power BI Desktop file.
Now we will experiment CALCULATE function to arrive at different sets of results.
Arrive one particular city sales total: Now, for example, assume you need to create a “New Measure” which gives one particular city total for an example “Columbia” city. This is where we need to use the CALCULATE function to apply calculation, right-click on the table and choose the “New Measure” option.
- Give the name to this measure as “Columbia City Sales”.
- Now open the CALCULATE function.
- An expression is the first option, in this example, we need to add the “Columbia” city total, so open SUM function.
- The Column Name that we need to SUM is “Sales Value Column”, so choose the respective column.
- Now SUM function adds the “sales value” together but in the Filter argument, we need to mention for which city we need to get the sum of sales, so open FILTER function.
- The Table that we are referring to is “Sales Table”, so first, choose the table name.
- For Filter Expression we need to select the “City” column and give the criteria as “Columbia”.
Ok, we are done close two brackets and hit enter key to get the new measure.
- Drag the Columbia city to fields to see the new measure.
Ok, now this measure gives the total sales of the city “Columbia” only.
You can cross-check the “Columbia” city total in Excel as well.
Like this, we can use the CALCULATE function in power bi to arrive at different kinds of results.
Now for an example assume for the city “Columbia” we need only the sales value for the state “South Carolina”, so this time we need to apply two filters.
- With the continuation of the previous DAX function close only one bracket and open another filter function.
- Once again mention the table we are referring to.
- This time we need to apply the filter for the column “State” and select the column and give the criteria as “South Carolina”.
- Sales Value of State South Carolina is shown below.
Now our new total will be 15099 i.e. this is the sales value for the state “South Carolina” in the city “Columbia”.
Now for an example, you want to find the percentage share of each city for the overall sales, this 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, now the idea is to get the overall sales total against all the city totals.
- So we need to create one more measure and that measure is as follows.
- Now insert “Table” visual first.
- For this table visual first add City name and Sales Value columns.
- As you can see above we have each city total here, and the overall sales value is 79393. Now drag and drop the new measure column “Overall Sales”.
- Now as you can see against each city we have “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.
- Now drag and drop this new measure to the table to get each city % share.
There you go we have a % share column. Like this using CALCULATE DAX function, we can arrive expressions based on different filters and conditions.
Note: Power BI Calculate Function file can also be downloaded from the link below and the final output can be viewed.
Things to Remember
- CALCULATE function in power bi is used to arrive at different results based on conditions.
- CALCULATE is always used with other DAX functions in power bi to get the job done.
This has been a guide to Power BI calculate function. Here we discuss how to use Calculate Dax Function in Power BI to apply other functions along with practical examples. You may learn more about Power BI from the following articles –