Totals: Why they don't always add up - and why that's OK

A common source of frustration is that totals in business intelligence (BI) don't add up. In the above example, we've drilled down from Gross Profit and intuitively most non-accountants would expect a total of 138:

100 - 5 + 50 - 7 = 138

Here's why a total of 52 is correct.

Credits and Debits

If you buy a cup of coffee for £4.00 with cash, a £4.00 credit is recognised - which ends up in a revenue account on the Profit and Loss (P&L) - and a £4.00 debit is recognised - which ends up in a cash account on the Balance Sheet. The sum of all the transactions roll up to give us a credit and debit amount per account (i.e., a credit of £100 for Trade revenue):

Accounts are treated according to their account type

Accounts have an associated type:

• Trade revenue and returns are revenue accounts

• Trade costs and discounts are expense accounts

The calculation for revenue and expense accounts is different:

• Revenue accounts are calculated as Credit less Debit

• Expense accounts are calculated as Debit less Credit

Calculating the total

The calculation for gross profit is revenue accounts (Trade revenue & returns) less the cost of sale expense accounts (Trade costs and trade discounts):

(100 - 5) - (50 - 7) = 52

BI tools (such as Power BI) should not perform the calculation this way as users invariably want to drill into the detail. Instead, it is advisable to calculate the sum of credits less debits (which is mathematically identical):

107 - 55 = 52

An advantage of BI is that you can drill down from a high level line item (i.e., gross profit) into the detail (the underlying accounts and individual records consisting of millions or billions of rows). Just remember, it won't necessarily add up ... but that's ok!