Power BI: How to 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.

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:

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:

Power BI: How to Calculate Average Value in Column
Power BI: How to Calculate Average and Ignore Zeros
Power BI: How to Use SUM IF

Leave a Reply

Your email address will not be published. Required fields are marked *