Table of Contents
Calculating the sum with a filter in Power BI is a process in which the sum of a specific set of data is determined based on a defined filter. This method allows for more precise calculations by only considering the data that meets the specified criteria. To perform this task, the user must first apply the desired filter to the data set, then use the SUM function to calculate the sum using only the filtered data. This results in an accurate and tailored sum value, providing valuable insights and analysis for decision making.
Power BI: Calculate Sum with Filter
You can use the following methods in DAX to calculate a sum with a filter in Power BI:
Method 1: Calculate Sum with One Filter
Sum Points =
CALCULATE ( SUM ( 'my_data'[Points] ), 'my_data'[Team] = "Mavs" )
This particular formula creates a new measure named Sum Points that contains the sum of values in the Points column only for the rows in the table where the value in the Team column is equal to “Mavs.”
Method 2: Calculate Sum with Multiple Filters
Sum Points =
CALCULATE (
SUM ( 'my_data'[Points] ),
'my_data'[Team] = "Mavs",
'my_data'[Assists] > 4
)
This particular formula creates a new measure named Sum Points that contains the sum of values in the Points column only for the rows in the table where the value in the Team column is equal to “Mavs” and where the value in the Assists column is greater than 4.
The following examples show how to use each method in practice with the following table in Power BI named my_data that contains information about various basketball players:
Example 1: Calculate Sum with One Filter
Suppose we would like to create a new measure that shows the sum of points scored by players only on the Mavs team.
To do so, click the Table tools tab, then click the New measure icon:
Then type the following formula into the formula bar:
Sum Points =
CALCULATE ( SUM ( 'my_data'[Points] ), 'my_data'[Team] = "Mavs" )
A new measure named Sum Points will be created that contains the sum of points only for the players on the Mavs team.
This will produce the following card that shows the sum of values in the Points column only for the players on the Mavs team:
From the output we can see that the players on the Mavs team scored a total of 75 points.
Example 2: Calculate Sum with Multiple Filters
Suppose we would like to create a new measure that shows the sum of points scored by players only on the Mavs team who also had more than 4 assists.
To do so, click the Table tools tab, then click the New measure icon:
Then type the following formula into the formula bar:
Sum Points =
CALCULATE (
SUM ( 'my_data'[Points] ),
'my_data'[Team] = "Mavs",
'my_data'[Assists] > 4
)
A new measure named Sum Points will be created that contains the sum of points only for the players on the Mavs team who had more than 4 assists:
If we’d like, we can display this value by going to the Report View in Power BI, then by clicking the Card icon under the Visualizations tab, then by dragging the Sum Points measure under the Fields label:
This will produce the following card that shows the sum of values in the Points column only for the players on the Mavs team who had more than 4 assists:
From the output we can see that the players on the Mavs team who had more than 4 assists scored a total of 35 points.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: