• 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.


    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(VALUES(tblCOA[AccGroup])= "Expenses",-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),
    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),
    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),
    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)
    [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!

    Comments 4 Comments
    1. vineeth's Avatar
      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
    1. mubb's Avatar
      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.

    1. amit4blog's Avatar
      amit4blog -
      Hi Ken,

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

      Like others I found your post very helpful - thanks.

      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?