Example

Use the CALCULATE function to sum transactions with revenue greater than $10,000.

            Large Transactions = CALCULATE(
                                     SUM(TransactionData[Revenue]),
	                                 TransactionData[Revenue] > 10000
	                            )
                

The first argument in the calculate function is the Expression that we want to return. After that we can add any number of optional Filter parameters. In this example:

• SUM(TransactionData[Revenue]) is the expression that gets returned.
• TransactionData[Revenue] > 10000 is the filter

Note that the filter columns are not limited to the same table. We can use columns from another table if needed. Just make sure you are using the correct syntax:

• 'TableName'[ColumnName]

It is optional to have quotations around the table name, ie. TransactionData. However, columns must always be surrounded by square brackets.

Multiple Filter Parameters:

The example below returns large transactions with medium ad budget:

                 Large Transactions & Mid_Budget = CALCULATE(
                                                 SUM(TransactionData[Revenue]),
	                                             TransactionData[Revenue] > 10000, 
	                                             Advertising[Ad Budget] = "medium" 
	                                            )
	            

In this case the filters are:

• TransactionData[Revenue] > 10000
• Advertising[Ad Budget] = "medium"

Note that the filters came from different tables. One filter from the TransactionData table and the other from Advertising. The two filters together results in an expression that returns revenue for large transactions and mid size ad spend.

Remember, filters act like AND conditions.

Using CALCULATE with OR conditions:

Although multiple filter parameters act like AND conditions it is also possible to "create" OR conditions in a CALCULATE expression.

The example below returns revenue with transactions greater than $10,000 for small of mid size ad spend:

                  Large Transactions & mid_or_small Spend  = CALCULATE(
                                                     
                     SUM(TransactionData[Revenue]),
	                 TransactionData[Revenue] > 10000, 
	                 Advertising[Ad Budget] = "medium" || Advertising[Ad Budget] = "small"
	                 )
                 

Try It Out

There is only two filters:

• TransactionData[Revenue] > 10000
• Advertising[Ad Budget] = "medium" || Advertising[Ad Budget] = "small"

But inside the second filter we are using the OR operator to select both small or medium size companies. (recall the || symbol is the OR operator). If I used && (AND operator) instead or had three filters, then the CALCULATE function would return 0. That is because in this data set you cannot have companies that are both small and midsize at the same time.

Using CALCULATE with ALL:

With any of the examples above, adding in slicers in the POWER BI canvas would further filter the data. However, it is possilbe to override the slicer commands using CALCULATE together with the ALL function. This could be useful when you don't want to filter the data any further for a particular slicer or when you want to calculate ratios and percentages.

The example below shows how to override the slicer command for a specific column:

                   Revenue from All_Regions = CALCULATE(
                              
                               SUM(TransactionData[Revenue]),
                               ALL(TransactionData[Region])
                               )
	        

If there is a slicer for the region column in the TransactionData table, then the slicer will be ignored and the full revenue amount will be returned.

The example below will override the filters for any column in the Advertising table

                     All_Ad_Revenue = CALCULATE(
                                
                                 SUM(TransactionData[Revenue]),
						         ALL(Advertising)
						         )    
	        

This will ignore all filters from slicers made from the columns in the Advertising table. However, slicers made from columns in another table will still be filtered. You can filter out as many columns or tables as you like using ALL. Just add them as additional filters.

The easiest way to return total revenue regardless of the filters applied is using ALL on the TransactionData table

                     Total Revenue = CALCULATE(
                                
                                 SUM(TransactionData[Revenue]),
						         ALL(TransactionData)
						         )    
	        

If there is a slicer for any column in the TransactionData table, then the slicers will all be ignored, and the total revenue will be returned. Note that if there is a slicer made from another column in a different table, that slicer will still be able to filter the data.

Try It Out

Using CALCULATE for percentages

This example shows the ratio of large transactions by Ad Source.

                     % of Large Transactions By Ad Source = DIVIDE(
                     
	                CALCULATE(SUM(TransactionData[Revenue]),TransactionData[Revenue] > 10000),
	                CALCULATE(SUM(TransactionData[Revenue]),TransactionData[Revenue] > 10000, ALL(Advertising[Ad Source])),
	                0) 
	        

Try It Out