Using HASONEVALUE in a DAX IF statement

As an accountant, I build financial reports, and one of the issues that we have to deal with is getting the numbers to display in a friendly format. Because of the way that debits and credits are stored in databases though, this can be a little challenging.

In this article, I’m going to walk through the process of building a simple profit and loss statement with PowerPivot, showing how to make all values show correctly. There are some certain key issues that we’ve got to work through though, and we’ll do that using a conditional DAX measure.

Background:

Before we jump right into it, let’s outline the data first. My basic PivotTable is set up as shown in Figure 1:

Click image for larger version. 

Name:	Figure1.jpg 
Views:	10879 
Size:	16.7 KB 
ID:	971

In this case, all items in the VALUES area is a simple measure built as such:


[Actual] =SUM(tblTransactions[Amount])

And the result, filtered down to January 2009 for my (sample) restaurant data looks like Figure 2:

Click image for larger version. 

Name:	Figure2.jpg 
Views:	10536 
Size:	52.3 KB 
ID:	972

Challenge #1 – Numbers are flipped

As you can see, all of my Actual numbers have negative numbers for the revenues and positive numbers for the expenses. This is due to the way that the numbers are stored in the database (credit balances are negative, debits are positive), but it doesn’t really display well.

It’s easy to flip everything: I just adjust the measure to be -SUM:


[Actual] =-SUM(tblTransactions[Amount])

Which will return the PivotTable shown in Figure 3:

Click image for larger version. 

Name:	Figure3.jpg 
Views:	10540 
Size:	52.5 KB 
ID:	973

So this is good, in that my revenues are now showing as positive, but I’d really prefer that my expenses display as positive too. I need to be careful though, as I don’t want to mess up the bottom line, which is correctly representing as a loss.

Challenge #2 – Flipping just the expense numbers

So what do we want to do here? Well, what we’re really after is to multiply the Actual measure for each row by -1 if it is an Expense item. Can we do this?
Naturally, we can. We just need to reach to DAX, and adjust our measure to use a DAX IF function, like this:


[Actual] =-SUM(tblTransactions[Amount])*
IF(
HASONEVALUE(tblCOA[AccGroup]),
IF(VALUES(tblCOA[AccGroup])= "Expenses",-1,1),
1)

Wow… so what does that all mean?

To evaluate this, let’s focus on the Alcoholic Beverages line first. We already know that:


-SUM(tblTransactions[Amount]) = 5425

So we can sub that into our DAX formula:


[Actual] = 5425*IF(HASONEVALUE(tblCOA[AccGroup]),
IF(VALUES(tblCOA[AccGroup])= "Expenses",-1,1),
1)

To evaluate the rest, let’s start from the IF(VALUES(tblCOA[AccGroup])= "Expenses",-1,1) portion and work our way out.

Basically what is happening here is that, for each line in the PivotTable, Excel is checking the values that have been filtered to see what is left in the tblCOA[AccGroup] column. It’s actually a little easier to see what’s going on if we convert our PivotTable to tabular view with repeating item labels, as shown in Figure 4:

Click image for larger version. 

Name:	Figure4.jpg 
Views:	10557 
Size:	80.4 KB 
ID:	974

So, looking at the $5,425, the formula is evaluating to see if the AccGroup column = "Expenses", which is plainly not the case (it shows “Revenues”.) It therefore returns 1 from the IF Statement. So now we’ve worked back to:


[Actual] = 5425*IF(HASONEVALUE(tblCOA[AccGroup]),1,1)

In this case, we can now see that no matter what the next IF test returns, it will yield 1 which, multiplied against 5425 will return 5425.

Now, let’s look at Cost of Sales, which evaluates to 9,894. If you go back to Figure 3, we can see that the first portion of our measure returns -9,894, so we can already assume the first part of the function:


[Actual] = -9894*IF(HASONEVALUE(tblCOA[AccGroup]),
IF(VALUES(tblCOA[AccGroup])= "Expenses",-1,1),
1)

Now, looking at Figure 4 again, we can see that this time tblCoa[AccGroup] IS “Expenses”. To that end, Excel returns -1, giving us the following:


[Actual] = -9894*IF(HASONEVALUE(tblCOA[AccGroup]),-1,1)

So now there is a potential change here, and we need to understand what this HASONEVALUE thing is all about…

The answer can actually be seen in the Restaurant Total and Grand Total lines. Notice how the AccGroup in both those cases is blank? It’s not because there isn’t one, it’s because there are multiple values, and Excel can’t portray that in a cell. So if we made our original function as follows:


[Actual] =-SUM(tblTransactions[Amount])*IF(VALUES(tblCOA[AccGroup])= "Expenses",-1,1)

Excel would choke with the following message: “A table of multiple values was supplied where a single value was expected.” The real rub though, is that if you checked the DAX in the formula builder, it says its fine! It’s only when it hits the issue when running that it gags.

At any rate, it seems that DAX can’t handle multiple values without a little more help, so we need to provide that. The way we do this is to test if there is one or more values contained in the field we are evaluating (in this case [AccGroup].)

So if you look back at the tabular view, you can see that all rows except the Restaurant Total and Grand Total have one value in them. (Revenues Total is still revenues.) To this end, HASONEVALUE(tblCOA[AccGroup] will return TRUE. Our formula therefore evaluates to:


[Actual] = -9894* -1 = 9894

As you work through this, you can see that none of the Revenue lines will flip the sign, but each of the Expenses lines, including the Expenses Total will. This is because in each of these cases, the AccGroup still has one value only (Revenues) and, being that it is Expenses, it will return the -1 to multiply against the SUM of [Amount].

What about the totals?

This is the cool part… As you can see from Figure 4, the totals are showing as negative values. Since expenses are higher than revenues, this is absolutely correct, but why?
Let’s walk that formula again, this time looking at the Restaurant Total line. But this time we’ll work from the outside in!
From Figure 3, we can see that:


[Actual] =-SUM(tblTransactions[Amount]) = -28,797

Which means we have:


[Actual] = -28797*IF(HASONEVALUE(tblCOA[AccGroup]),
IF(VALUES(tblCOA[AccGroup])= "Expenses",-1,1),
1)

Next comes the HASONEVALUE test. Looking at the AccGroup column in Figure 4, do we have one value there? The answer is no, we have a blank. (No values are shown because it’s too complex to display that we have multiple values.) So that means we can evaluate our formula as:


[Actual] = -28797*IF(HASONEVALUE(tblCOA[AccGroup]),N/A,1)

Or


[Actual] = -28797*1 = -28797

And, now that we know that everything is calculating correctly, we can switch the PivotTable back to Compact layout. In addition, let’s add Feb and Mar to the table as well, giving us the result shown in Figure 5:

Click image for larger version. 

Name:	Figure5.jpg 
Views:	10548 
Size:	93.5 KB 
ID:	975

Perfect! It’s rolling those DAX formulas right through the whole PivotTable. Even though our Grand Total column and row have more than one month, the key is that the AccGroup column still only has one value, so our formulas continue to work!

Share:

Facebook
Twitter
LinkedIn

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts

Excel Fundamentals Boot Camp

COACHED TRAINING: Excel Fundamentals Boot Camp Course Description In the Fundamentals Boot Camp, you will begin with a review core skills for the Excel analyst. This section is geared to

Read More »