Power Pivot Principles: Introducing the CALCULATETABLE Function

Welcome back to our Power Pivot blog.  Today, we discuss the CALCULATETABLE function.

The CALCULATETABLE function is somewhat similar to the CALCULATE function in Power Pivot, in that it applies filters to the data returned with a calculated value.  The main difference between the two is that the CALCULATETABLE function returns with a table whereas the CALCULATE function returns with a scalar value.

The CALCULATETABLE requires the following syntax to operate:

The <expression> is the table to be evaluated, and the <filter> is a column that can be located in any table imported to Power Pivot.  In order for the filters to work they have to be from tables that have proper connections established in the data model.

Example

Let’s create a measure that calculates the sales for division 3 in the year 2020:

Using the CALCULATETABLE formula alone will not work, as Power Pivot will return with the error: 

As it stands above, the current CALCULATETABLE measure returns with a table looking like this:

Power Pivot can’t aggregate an entire table into a single cell.  We have to specify which expression we want to be evaluated.

We have to use the SUMX function. To refresh your memory, the SUMX function ‘returns the sum of an expression evaluated for each row in a table’, it requires the following syntax to operate (you can read more on the SUMX function here):

With that in mind we can adjust our previous measure:

The resulting PivotTable:

Of course, this result can be achieved using the CALCULATE function:

As it stands the CALCULATETABLE and the CALCULATE functions are quite similar. The key difference is their outputs. We would use the CALCULATETABLE function when we need to use other functions that expect a table as an expression and CALCULATE when we use functions that expect a single value.

That’s it for this week, happy pivoting!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.