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]) |

# 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]) |

# 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) |

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

-SUM(tblTransactions[Amount]) = 5425 |

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

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) |

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) |

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

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) |

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 |

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

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

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

[Actual] = -28797*1 = -28797 |

## vBulletin Message