Save content
Have you found this content useful? Use the button above to save it to your profile.
ABC of Power BI: N is for Negative Numbers | accountingweb
istock_dilok_klaisata_negative_number

ABC of Power BI: ‘N’ is for Negative Numbers

by

Continuing his ABC of Power BI series, Hugh Johnson looks at negative numbers, expenses and liabilities.

19th Apr 2023
Save content
Have you found this content useful? Use the button above to save it to your profile.

I have always thought that the way accountants treat negative numbers is weirdly inconsistent. A profession that takes pride in precision and clarity is perfectly happy with the notion that Operating Costs reported by two different companies as £10m and –£10m are probably the same amount. 

If you are using a tool like Power BI to produce financial reports for many different companies, then you will need a strategy to handle these differences easily.

To start with, let’s break down a Power BI report into layers.

1. Three layers of a Power BI report

Three layers of a Power BI report
hughjohnson

Dataset layer

By the Dataset layer, I mean everything that is done to get data from the raw data sources such as tables from accounting software, data warehouse, Excel files and so on, and transform these sources into a data model with tables and relationships to use in reports. I do most of this work using Power Query (with a preference to use the online version in Dataflows and/or Datamarts, which enable me to prepare tables in one place that I can use across multiple reports). The final step in building this layer is to use Power BI Desktop to define the table relationships and any roles/row-level security in the data model for a specific report.

Calculation layer

In the top two layers of my reports, I use DAX measures to summarise the data for final presentation through the Power BI visuals. I generally avoid creating Calculated Tables and Columns (preferring to prepare these using Power Query in the Dataset layer). 

The calculation layer is a set of measures that always deliver summaries based on the following polarity rules.

Account Type Default Balance Default Movement
Income Positive Positive
Expense Negative Negative
Asset Positive Positive
Liability Negative Negative

 

I also split the calculations layer into two sub-layers; Base Measures and Aggregations. The Base Measures make sure that the table above is adhered to but ignore any additional filters that I may need to apply such as date ranges, or whether the calculation is “Actual” or “Budget”. I also use a special set of Base Measures that calculate the date range based on a set of slicer selections, but this is a separate topic.
For example, if I were using a Journals table that contained all the accounting journal entries by account, date and amount, then a Base Measure to calculate either a P&L amount or a Balance Sheet Movement might look like this:

[BaseAmount] =
SWITCH (
SELECTEDVALUE ( ChartOfAccounts[Report] ),
“pl”, SUM ( Journals[Amount] ) * -1,
“bs”, SUM ( Journals[Amount] )
)

Where ChartOfAccounts[Report] is a column that indicates whether the row belongs to the Income Statement or the Balance Sheet.

Presentation layer

It is only in the top presentation layer that I determine how to display a value. This will include whether to display expenses as positive or negative values, how to display negative values, whether to show values in £s or £ks and maybe other elements such as font colours.

2. The advantages of standardising the calculation layer output

Variance calculations

Probably the biggest advantage of standardising the calculation layer output, as in the above table, is that it makes variance calculations very simple to do and their results very simple to interpret. By following these rules, then:

  • variance is always Actual minus Comparison
  • negative variance is always bad
  • positive variance is always good.

How I choose to present a variance is a separate issue that I handle in the presentation layer, but the consistency of the calculation layer (negative = bad and positive = good) makes this very easy to handle.

Balance, movement and ratio calculations

Any aggregations of balances and movements are also made very simple by following the rules in the above table. These calculations are typically simple “SUM”s. For example:

  • Profit is always income + expense
  • Cashflow is always receipts + payments
  • Balance Sheet Movement is always Asset Movement + Liability Movement
  • Balance Sheet Balance is always Asset Balance + Liability Balance
  • Any ratio (such as Operating Expenses as a proportion of Income) is always a simple division, like Operating Expenses ÷ Total Income.

Again, how I choose to present expenses, payments, liabilities and their ratios is a separate issue for the presentation layer.

3. Presenting the final result

By following all the rules above, there is no muddled thinking, and I can use a final set of measures to display the values in my report in the format that I want.

Consider the Income Statement shown below.

The advantages of standardising the calculation layer output
hughjohnson

 

Consider that in the report I have the following Measures in my calculation layer:

  • [PL_Actual] that is a P&L calculation that returns a positive value for income and a negative value for expense for the selected period.
  • [PL_AllSales] returns the total sales value for the selected period.
  • [PL_Previous] returns a P&L calculation for the previous selected period (in this case the selected period of the previous fiscal year), again with a positive value for income and a negative value for expense.
  • [PL_Variance] = [PL_Actual] – [PL_Previous]
  • [PL_Ratio_AllSales] = [PL_Actual] ÷ [PL_AllSales].

Using the [BaseAmount] measure previously referred to, then as an example, [PL_Actual] might be something like this:

[PL_Actual] =
Var MaxDate = [MaxDate]
Var MinDate = [MinDate]
Return
CALCULATE (
[Base_Amount],
ChartOfAccounts[Report] = “pl”,
ALL ( Dates ),
Dates[Date] <= MaxDate,
Dates[Date] >= MinDate
)

Where [MaxDate] and [MinDate] are special Base Measures that return the maximum and minimum date values of the selected range from the slicers.
The rows for the Income Statement come from a table called “ChartOfAccounts”. In this table I have the following columns:

  • ChartOfAccounts[Group] and ChartOfAccounts[Category] that give the rows of the Income Statement.
  • ChartOfAccounts[Multiplier_Display] that has a value of 1 or –1 for each row and is used to indicate whether to multiply result by 1 or –1 before display.

In the presentation layer then, ignoring any formatting such as % or (negative values), each column in the Income Statement is the appropriate Measure from the calculation layer multiplied by SELECTEDVALUE ( ChartOfAccounts[Multiplier_Display] ). So, the “Actual” column could be returned by a Measure such as this:

[Display_PL_Actual] =
[PL_Actual] * SELECTEDVALUE ( ChartOfAccounts[Multiplier_Display] ) 

I can follow the same pattern for the other Income Statement columns. In the example, I have also applied additional formatting to show negative values in brackets and zero values as “-“.

Finally, for the condition formatting, I have simply chosen to format each ChartOfAccounts[Category] row based on a “Field Value” that is [PL_Variance] with red for negative, white for zero and green for positive. In this way, even if I switch the presentation of variance on expenses so that in the example, I show the variance for purchases as £146,866, I will still get the highlighting as red, showing that this increase in costs is a negative effect.

4. Report flexibility and variations

With a separated presentation layer like this, and using an external metadata spreadsheet to set the ChartOfAccounts[Multiplier_Display] column values in the report, I can switch how I want to display expenses, payments and liabilities in a couple of minutes.

Following the same principle, it is possible to configure how to display ratios, decimal places, font colours, row indentations and so on.

5. Summary and other things to consider

  • Separating DAX measures into separate layers for “Calculation” and “Presentation” enables greater flexibility to handle variations in how to display a financial report. It also makes your reports more maintainable and adaptable to different client needs.
  • Extracting report settings into an external “Metadata” spreadsheet extends this flexibility.
  • In this article I have emphasised the value of having a consistent “Calculation” layer with expenses, cash-out and liabilities as negative values. Generally, I also standardise the data model when it comes to these values, but with the income statement values reversed so that a trial balance aggregation across all accounts for a given period will return zero.
  • Pay a lot of attention to your data sources, as these can be inconsistent. Data sources often have opposite signs for budget expenses vs actual expenses, sometimes even for actuals. The Invoices table in Xero, for example, has Sales Invoices and Purchase Invoices all as positive values in the same table. 
  • Putting the work in at the beginning to get the data model right and your calculations level correct will pay dividends in the future.

6. Useful links

Tags:

Replies (1)

Please login or register to join the discussion.

Routemaster image
By tom123
20th Apr 2023 16:28

Thanks for this. I first learned about Pivot Tables 20 years ago (on Accountingweb), and have used them ever since.

Latterly, I am attempting to upgrade to using Data Models etc in Excel - and separate files for data and reports.

I feel a little like when I first set eyes on a Pivot Table - ie confused - but am keeping going

Thanks (0)