560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!).
For this page, I'll show how to:
remove a single filter from a formula; and
removing two or more filters from a formula.
Let's start with removing a single filter.
DAX takes a lot of getting used to! If you want to meet a real owl, why not book a place on our dedicated two-day DAX course?
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] ) /
SUM( 'Transaction'[Quantity] ),
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%.
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]) /
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?
|Parts of this blog|
25 Aytoun Street