Introduction:
Calculate function is a critical tool in Power BI that empowers users to create advanced calculations and customized metrics that go beyond basic analytics. By mastering the Calculate function, users can unlock the full potential of Power BI and create powerful, dynamic dashboards and reports.
Syntax:
CALCULATE(expression, filter1, filter2, …)
The expression is the formula or calculation that will be modified, and the filters define the context in which the expression should be evaluated. Users can include multiple filters, allowing for complex filtering and manipulation of the data.
Basic usage of the Calculate function:
- Modifying or Filtering Data: The Calculate function can be used to modify or filter data within a formula. For example, it can be used to create a calculation that only includes data from a specific date range or exclude certain categories of data. This allows users to customize the data to meet their needs.
- Creating Simple Measures: Users can create simple measures using the Calculate function by specifying the calculation they want to perform. For example, they can create a measure that calculates the sum of a particular column or the average of a specific set of data. This provides users with a quick and easy way to perform common calculations.
DAX syntax for the CALCULATE function in Power BI:
CALCULATE(<[expression]>,([filter1],([filter2],([filter…]))))
Example:
Right-click on the table, and choose the “New measure”
- Give the name to this measure “Calculate_Function”
- Open the CALCULATE function
- Open the SUM function(An expression)
- Choose the respective column we need to SUM
- Column for FilterExpression and give the criteria
- Drag the new measure to “Fields”
- This measure only gives the total unitprice of the category “Seafood”
- To cross check we can see the values in Table as well.
