DAX: CALCULATE
DAX: CALCULATE
Knowing how to use the CALCULATE function is like knowing a cool super power. We’re going to learn the basics of it today, but it’s extremely versatile.
The CALCULATE function performs a calculation on data but then lets you specify what data you want that calculation done on.
Let’s try it out.
We’re going to use a very simple dataset to demonstrate how the CALCULATE function works.
The video below (and the written instructions below) use a simple Power BI file with one simple table. If you’d like to download a copy of it to use yourself, you can download it here.
If you’d like to use the same data that I do in the below video, here is the data file you can connect to Power BI Desktop:
Video:
We have here a table with all the columns in our simple dataset, namely Region, Program, Participants and Cost per Participant. At the bottom you can see a Totals row that sums up our 2 numeric columns.
If we wanted to figure out the sum of just the number of participants in our Midwest region, we *could* filter this table (see the Filter Pane tutorial for one way to do that), but sometimes you need to visualize ALL the data (like we are here, but figure out a calculation like SUM on a subset of the data.
That’s where the CALCULATE function comes in handy.
Let’s calculate the SUM of participants from our Midwest region.
Open up a new measure by clicking New Measure up in the ribbon.
First we’ll name our measure “SUM MIDWEST PARTICIPANTS” and follow it by an equal sign.
Then we’ll type CALCULATE followed by an opening parathesis “(“.
A CALCULATE function has 2 parts to it. The 1st part is where we state what calculation we want it to perform, and the 2nd part is where we tell it what data we want it to do the calculation on.
Let’s do the first part. We want it to SUM our Participants, so we write a SUM function for Participants, and then follow it with a comma. The comma tells Power BI that we’re done the first part.
The 2nd part is where we write a little rule about what data we want this performed on. We want it to happen for just our Midwest region, so we specify what column to look at and what to look for within that column.
We’re basically created a SUM calculation with a filter for our Midwest region.
Click OK and you’ll see your new measure in your Data Pane
Let’s test this out by adding a Card Visual to our canvas and dragging our new measure to it to see what we get.
We get 40, which is exactly the number of participants in our Midwest rows. They are the first 3 rows in our table.
We can do something similar with other types of aggregations. Let’s try an AVERAGE function within our Measure. Click our measure in the Data Pane, so we get our DAX measure in our Formula Bar, and highlight all the code. Then hit CTRL-C to copy it to your clipboard.
Then, click the New Measure button in the ribbon to create a new measure where we can paste in what we just copied. Then we just have to adjust the title and the calculation a bit.
Change the name of the measure to “AVG MIDWEST PARTICIPANTS” and change the SUM function in the first part of the Calculate formula to say AVERAGE. Now it’ll figure out the Average number of Participants per program in the Midwest.
Press return to complete your DAX, and let’s bring this measure to a Card visual and see what it gets us:
13.33 is the average number of participants in each program in the Midwest.
That’s it. That’s the CALCULATE function.
Let me know if you have questions about it in the comments below, and we’ll figure them out!
Take care,
Joe.
Post Tags: DAX | Filters | View all Post Tags
Contact Joe | TraversData.com | People-Friendly Power BI | LinkedIn
This post was made with the Feb 2024 version of Power BI Desktop.