Table of Contents
To calculate the sum using a filter in Power BI, first create a visual in your report that displays the data you want to sum. Next, add a filter to the visual by selecting the field you want to filter by. This will narrow down the data displayed in the visual. Then, in the formula bar, type in the SUM function followed by the name of the field you want to sum. This will calculate the sum based on the filtered data and display it in the visual.
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: