• # 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:
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:

# 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: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: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].

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: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!

I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now! 4 Comments
1. vineeth -
hi ken,

this is an excellent article. I have been looking all over for an explanation of HASONEVALUE. This will help me complete some of the reports i was building for finance myself.

Thanks and Regards
Vineeth
1. mubb -
Hi Ken,
Really enjoyed your video at PowerPivot School. Thank you for great, detailed instructions. This post is also very illuminating and useful. One quick question.... the formula
IF(VALUES(tblCOA[AccGroup])= "Expenses",-1,-1)
should it not be IF(VALUES(tblCOA[AccGroup])= "Expenses",-1,1)
this seems to be the case the first time it was introduced but then uses -1 in second argument, for which I didn't understand the logic/reasoning, your guidance would be most appreciated.

Many thanks.... and keep up the good work.

Regards,
Mubbasher.
1. amit4blog -
Hi Ken,

It's a great post. I am looking some extra ordinary article, which i found here.Thanks for publishing.
1. rwilson -
Hi Ken

One thing I am having a bit of trouble with is taking this down to another level ie the next account sub group. For example, what if "Alcoholic Beverages" was broken down into "Beer", "Wine", "Spirits" etc and "Cost of Sales" was broken down into "Food", "Beverages", "Condiments" etc.

I can get the individual items eg "Beer" and "Food" to display correctly, but not the subtotals for "Alcoholic Beverages" or "Cost of Sales". Any thoughts on how this might be done?

Cheers
• • ### Recent Forum Posts

#### VBA Code to Track Changes in Excel Sheet

MyForcey 2022-06-23, 01:40 PM 