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:

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:

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:

Wow… so what does that all mean?

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

So we can sub that into our DAX formula:

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:

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:

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:

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:

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:

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:

Which means we have:

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:

Or

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!