SAGE 50 ACCOUNTS UK TO POWER BI: SYNTHETIC P&L EXAMPLE
Turing BI | prepared 4 October 2026

PURPOSE

Follow 11 September activity lines from nominal mapping to seven P&L figures.
The sample includes a sales credit note and two expense credits, plus boundary
rows outside the reporting period. Use it to inspect signs, scope and totals.

This is synthetic NORMALISED nominal activity for an educational reporting
example. It is not a raw Sage export, a Sage database schema, a connector, a
balanced journal, or a client result. Account codes and labels are illustrative.
All amounts are GBP, with VAT and control-account movements excluded.

FILES

nominal-activity.csv   15 synthetic P&L lines, including four boundary rows.
nominal-mapping.csv    Seven illustrative nominal codes and their P&L groups.
SagePL.pq             Power Query M for one imported table named SagePL.
measures.dax          Eight separate DAX measures, including the row count.
expected-results.csv  Expected totals for the synthetic September fixture.
README.txt            Setup, reconciliation and limitations.

BUILD THE LOCAL EXAMPLE

1. Use Power BI Desktop on Windows. Extract the ZIP into a local folder such as
   C:\Sage50PowerBIExample. Keep both CSVs together. These files use UTF-8,
   comma delimiters, ISO dates (YYYY-MM-DD), and a decimal point.

2. In Desktop, create a Blank query from Get data. In Power Query's Advanced
   Editor, replace the starting code with the full contents of SagePL.pq.
   Edit FolderPath to your extracted folder, without a trailing backslash.
   The query reads the two CSVs from your local computer.

3. Name the query exactly SagePL. Confirm the preview contains 11 rows, from
   SYN-003 to SYN-013. The period is TransactionDate >= 2026-09-01 and
   TransactionDate < 2026-10-01. August 31 and October 1 rows are excluded.
   Keep the query enabled for load, then choose Close & Apply.
   This query is fixed to September 2026. To report another month, provide
   the matching activity extract and change both PeriodStart and
   PeriodEndExclusive in the M query.

4. On the loaded SagePL table, use New measure to add each definition from
   measures.dax separately. Start at 'Revenue GBP =', through its closing
   parenthesis; then add the next definition in a new measure. Keep the names
   exactly as supplied, because the profit and margin measures refer to them.

5. Format the five GBP measures as currency, GBP, with two decimal places.
   Format Gross margin % and Operating margin % as percentages with one decimal
   place. Format Included rows as a whole number.

6. Add a card for each of the seven financial measures, plus Included rows.
   With all page, report and visual filters cleared, compare the cards with
   expected-results.csv. A ratio of 0.625 should display as 62.5%, not 0.6%.

7. Add a detail table with ActivityRowID, TransactionDate (the date field,
   not its automatic date hierarchy), NominalCode, NominalName, PLGroup,
   DebitGBP, CreditGBP and SignedAmountGBP. Use 'Don't summarise' for the
   amount fields and turn off totals on this detail table: adding revenue and
   positive expense display values together is not a profit calculation.

EXPECTED SEPTEMBER RESULTS

Revenue GBP             48,000.00
Cost of sales GBP       18,000.00
Gross profit GBP        30,000.00
Gross margin %              62.5%
Overheads GBP           12,000.00
Operating profit GBP    18,000.00
Operating margin %          37.5%
Included rows                 11

TRACE THE CREDITS

Revenue is credit minus debit: 32,000 + 18,000 - 2,000 = 48,000.
SYN-012 is the sales credit note, represented by a debit to the revenue code.

Cost of sales is debit minus credit: 12,000 + 6,500 - 500 = 18,000.
SYN-010 is a supplier credit against the illustrative materials account.

Overheads are debit minus credit: 2,000 + 800 + 900 + 8,500 - 200 = 12,000.
SYN-011 is a credit against the illustrative software account.

Gross profit = revenue - cost of sales = 30,000.
Operating profit = gross profit - overheads = 18,000.
Each margin divides its profit by revenue. DIVIDE returns blank for a zero
denominator. These are the definitions for this small example; operating
profit here is before finance costs and tax, which are outside its scope.

QUERY CHECKS AND MODEL SHAPE

ActivityRowID identifies a normalised nominal LINE, not necessarily a whole
source transaction. TransactionDate is a date. NominalCode remains text.
DebitGBP, CreditGBP and SignedAmountGBP use fixed-decimal currency types.
Each activity row has one positive debit or credit; the other amount is zero.

The M query checks the supplied column names, required values, duplicate row
IDs, duplicate mapping codes, supported P&L groups and missing nominal mappings.
It validates the full input before selecting the reporting period. Conversion
errors in malformed dates or amounts also stop the query.

The two CSVs join on NominalCode before loading. Only SagePL is loaded, with:
ActivityRowID, TransactionDate, NominalCode, DebitGBP, CreditGBP,
NominalName, PLGroup, SignedAmountGBP.
There is no relationship to configure in this single-table learning model.

Revenue's SignedAmountGBP is credit minus debit; expense SignedAmountGBP is
debit minus credit. The measures filter by PLGroup before summing this field.
KEEPFILTERS respects existing filters. Use an unfiltered report to reproduce
the stated totals. Category selections can change the displayed margins.

BEFORE ADAPTING THIS TO A SAGE FILE

Sage 50 Accounts UK offers a Transaction CSV (Extended) report through
Transactions > Reports > Transactions > Transaction CSV (Extended), then
Export, choose CSV, and confirm the report criteria. See Sage's source below.

That export does not automatically match these sample columns. Check the
fields, transaction types, date basis and nominal detail available in your
version. A normalisation step must handle nominal splits, credits, reversals
and posting corrections, and create a stable unique ID for each nominal line.
Do not rename a transaction total as a nominal-line amount without checking
how it was split and posted.

Agree the nominal-code mapping with the person responsible for the accounts.
Exclude balance-sheet/control accounts and VAT from this P&L subset. Include
the relevant P&L adjustments and use the same entity, period, date basis,
posting status and account coverage when reconciling to Sage's reports.

This example filters by transaction date in a calendar month. An accounting
period, posting date, adjustment period or financial year can require a
different rule. Changing the sample dates alone does not resolve that choice.

KNOWN LIMITS AND VALIDATION

The model is a single-currency, one-month teaching example with synthetic
activity. It omits balance sheets, opening balances, cash flow, VAT, stock
valuation rules, accrual policies, finance costs, tax and consolidation.
It is not a complete set of financial statements or accounting advice.

The downloadable CSV arithmetic, mapping coverage, boundary rows and ZIP
contents were checked independently using a dependency-free Python validator.
The M and DAX were reviewed against Microsoft documentation. They have not
been executed in Power BI Desktop or the Power BI Service in this environment.
Validate the query and measures in your own Desktop installation before use.

There is no .pbix report in this bundle. It does not connect to Sage, publish a
report, configure sharing, or set up scheduled refresh. A service refresh of
local files needs its own supported access and refresh configuration.

SOURCES

Sage: exporting transactions (Sage 50 Accounts UK)
https://gb-kb.sage.com/portal/app/portlets/results/viewsolution.jsp?solutionid=200427112357443

Microsoft: Text/CSV connector
https://learn.microsoft.com/en-us/power-query/connectors/text-csv

Microsoft: Power Query data types
https://learn.microsoft.com/en-us/power-query/data-types

Microsoft: Csv.Document and Table.NestedJoin
https://learn.microsoft.com/en-us/powerquery-m/csv-document
https://learn.microsoft.com/en-us/powerquery-m/table-nestedjoin

Microsoft: CALCULATE, KEEPFILTERS and DIVIDE
https://learn.microsoft.com/en-us/dax/calculate-function-dax
https://learn.microsoft.com/en-us/dax/keepfilters-function-dax
https://learn.microsoft.com/en-us/dax/divide-function-dax

Guide: https://turingbi.com/insights/connect-sage-to-power-bi
