Totals: Why they don't always add up - and why that's OK
- Chris Barber
- 4 days ago
- 2 min read

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!
コメント