How to use the CALCULATE function in DAX measure formulae
Part two of a four-part series of blogs

The CALCULATE function is the most important one in DAX. This blog shows you how to use it to replace, remove and amend the query context for a measure (and also explains what this sentence means!).

1. The CALCULATE function in DAX measures
2. Removing filters in an expression using CALCULATE (this blog)
3. Using the CALCULATE function to replace filters
4. Using the VALUES function to amend the query context

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 12 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Removing filters in an expression using CALCULATE

• remove a single filter from a formula; and
• removing two or more filters from a formula.

## Removing a single filter - our example

Suppose that in a pivot table you want to display total sales as a percentage of total sales for all species: The figure for the cell shown shaded should equal 39 (total sales for amphibians for the East quadrant for Retail Park centres) divided by 430 (the same figure, but for all species).  This equals 9.07%.

Any ratio such as the one above can be expressed as A / B, where:

• A is the numerator; and
• B the denominator

Memories of school maths trickle back?  In this case the expression to calculate is: Take the total sales for the current species, quadrant and centre type, and divide this by the total sales for all species for the current quadrant and centre type.

We're relaxing the species constraint which would otherwise be imposed by the pivot table cell's position within the pivot table.

Here's what our formula should look like: Calculate the total quantity for the current query context, and divide it by the same figure but without any species constraint (ie for all species).

If you want to try copying it, you could use this:

Fraction of all species: =SUM([Quantity] ) /

CALCULATE(

SUM( 'Transaction'[Quantity] ),

ALL(Species)

)

Here's what this should give: As a quick sense check, it's vital that the grand totals at the bottom of each column are 100%.

## Removing two or more filters

You can have as many additional filters as you like in the CALCULATE function.  For example, you might want to show total sales for a particular pivot table cell as a percentage of total sales for that pivot table cell's query context, but for all quadrants and all species.

Here's what this looks like as a fraction, using tuples: Divide the figure for the current query context by the figure for the current query context without any quadrant or species constraint.

Here's the formula we could use:

Fraction of all species: =SUM([Quantity]) /

CALCULATE(

SUM('Transaction'[Quantity]),

ALL(Species),

)

Here's what this would give for our pivot table: The figure for amphibians in the East quadrant is 39 (total sales for amphibians for the East quadrant for Retail Park centres) divided by 4806 (total sales for all species for all quadrants for Retail Park centres).  This equals 0.811%.

You can use the ALLEXCEPT function to remove every filter but one from a formula (although to get it to work you first seem to need to combine the fields you're filtering by into a single table, using the RELATED function).

So much for removing filters; what happens if you want to replace them?