WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 519 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

How to use the CALCULATE function in DAX measure formulae
Part three 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
3. Using the CALCULATE function to replace filters (this blog)
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.

Using the CALCULATE function to replace filters

The previous part of this blog showed how to remove filters altogether from the query context; this part shows how to keep a filter, but change it.

Our worked example - sales as a percentage of Southern sales

Suppose we want to show the following pivot table:

We're showing sales for any cell as a percentage of the equivalent sales for the South region. The South column necessarily always equals 100%.

A quick reminder of the underlying sales figures:

The 34.82% for the East quadrant shown shaded in the pivot table at the top of this page equals 39 (the figure for East quadrant amphibian sales for Retail Park centres) divided by 112 (the figure for South quadrant amphibian sales for Retail Park centres).

Tuple-wise (strangely, my spell-checker wasn't keen on that word) we're showing for the shaded cell the results of this calculation:

The tuple shows we're changing, not removing, the quadrant filter.

The formula to use

Here is a formula which would give the required results:

Fraction of South:=SUM([Quantity]) /

CALCULATE(

SUM('Transaction'[Quantity]),