Fixing Incorrect Totals in DAX

Typically in Power BI visuals like a matrix or a table, and in pivot tables in Excel, we expect the totals to be the sums of the individual rows. This is probably because we are so used to adding up columns in Excel.

In this example image (below) I've written a measure called [Target] to indicate with a 1, months where my target has been met. As you can see there are 7 months that meet that target. You probably expect the 2012 total to be the sum of the values for each month in 2012, but as you can see, that isn't the case.

I'm going to look at why this is and show you how to get your totals adding up as you want them to.

Sample Data Setup

I'm using data from Microsoft's Adventure Works sample database. Here's a snapshot of what the data looks like

I've also got a basic Date Table

with a relationship between the two tables like this

Measuring Monthly Targets

Lets say I want to look at months where I've met a particular target. I want to check how many months where the TotalDue is greater than $3,000,000.

I've written a measure called [Total Amount] which is

I create a new measure called [Monthly Target Met]

What I want to do is check the [Total Amount] for each month and if it exceeds $3,000,000 then give me a 1. I can then count up all the 1's to get a total for the number of months in the year that exceeded the target.

After adding this measure to my matrix I get this

In 2012, there are 7 months that exceeded $3,000,000 but the total row for 2012 shows 1, not 7. Why is this?

Let's look at the DAX I wrote. I've said that if [Total Amount] is > 3000000 then give me a 1. The key here is to remember the filter context for the measure.

The measure is worked out subject to the filter context for the cell that the measure is in.

Looking at the matrix, the monthly cells have a filter context that says, only check the total amount for that month.

Looking at the yearly totals the filter context here says check the [Total Amount] for that year. It's not saying check the Total Amount for each month in that year. This is where our intuition trips us up.

We expect it to be checking each month in the year because that was the intention of our measure, to check for months over $3,000,000.

However, all it's doing is checking the [Total Amount] for the cell it's in in the matrix, subject to that cell's filter context.

If we want the totals to count the number of months within the year that exceed our target then we need to use the iterator SUMX to check each month and count the ones that exceed $3,000,000.

So back to our measure. Using SUMX, it requires a table as its first argument and that table will be the 'DateTable'[Month Year] column because I want to work out the sum at the granularity of month in each year.

SUMX's 2nd argument is the expression to evaluate and that is the same IF I started with. The measure now looks like this

Checking the matrix, you can see that the totals are now adding up to the number of months within that year that exceed $3,000,000.

The measure still works for each individual month too because SUMX is only checking/counting that month (due to the filter context)

TIP : Check What a Table Function Creates

I've used the VALUES function to pass in the [Month Year] column from the Date Table. As with any function that creates a table (like FILTER) you can see exactly what it is creating by using the function code to actually create a new table.

To do this, from the Modeling tab click on New table

Paste in the code that creates the table

Check the new table in the Data view

Checking Daily Targets

Checking for a daily target uses a very similar approach to the monthly target, but because I want to check [Total Amount] at the day level rather than the month level, I need to change the dates used by SUMX to do its calculation.

I'll call the new measure [Daily Target Met] and this is the code

I'm passing in the entire Date Table as the first argument to SUMX because I want it to check the [Total Amount] for every date.

For the daily target, I'm checking for days where [Total Amount] exceeds $25,000.

After adding the new measure to my matrix I get this

I don't have dates added in the rows of the matrix but the measure is working because it iterates over every date. For example in June 2011 it checks all 30 days and counts 4 days where the [Total Amount] exceeds $25,000.

I can add the dates to the matrix rows

I can now see each individual date and confirm that the measure is working as I expect.