Measure for Measure

When working with accounting data in Power Pivot one option is to make each account a measure; this can work if you have only a few accounts, such as [Measures].[Sales], [Measures].[Cost of Sales], Measures].[Overheads]. Using measures as accounts in this way also makes it easier to do modeling in Power Pivot. However things get complicated if you require time intelligence calculations such as QTD, YTD, same period last year etc. Then you need to create measures / calculated fields for [Sales MTD], [Sales YTD], [Sales Last Year], [Cost of Sales QTD], [Cost of Sales YTD], and so on.

So if you have a chart of accounts (COA) with more than a handful of accounts you should make it a dimension (or lookup) table in your Data Model, probably imported as a download from or query against the ERP or accounting system. This has some advantages in that you can create properties related to each account, such as sign (whether it normally has a debit or credit balance), and whether it is a balance account (such as receivables or payables) or a flow account (such as revenues or expenses).

You will still need to have at least one measure in your model; this can be something like Value or Amount. And if you require the types of time intelligence just described, you can create a measure / calculated item called [QTD Value], [YTD Value], [Last Year Value] etc. either using the built-in DAX time intelligence functions or by creating your own DAX calculation (if you have a custom calendar).

Unfortunately Power Pivot (and Microsoft Analysis Services Tabular) does not support Calculated Members the way Analysis Services Multidimensional does. In simple terms, this means that in Multidimensional you can add calculations to a dimension that are not measures. You can add a new member ([Gross Profit] in this example) to the Accounts dimension as follows (code syntax is not precise):

[Accounts].[Gross Profit] = [Accounts].[Sales] - [Accounts].[Cost of Sales]

In Power Pivot or Tabular, the equivalent calculation would require a Measure / Calculated Field:

Gross Profit:='Accounts'[Sales] - 'Accounts'[Cost of Sales]

More likely, 'Accounts'[Sales] would be the sum of a range of revenue account numbers and similarly for 'Accounts'[Cost of Sales].

This makes it difficult to create an income statement starting with a PivotTable that incorporates both revenue and expense accounts and calculations like [Gross Profit] and [Net Profit] in a Profit and Loss report layout.



© 2011-17 Microsoft and Excel are registered trademarks of Microsoft Corporation in the USA and other countries.
Template design by Andreas Viklund

Terms of Use            Privacy            Cookies            Contact