Build a Custom Profit and Loss Layout in Power BI for SYSPRO

ERP systems can produce Profit and Loss reports, but they do not give users control over the layout: the sort order, hierarchies, formatting, custom wording of headings and totals, or which lines to include and exclude. Accountants and finance professionals demand that flexibility. A User Defined Mapping table, or UDM, gives developers and report designers a platform to build the Profit and Loss dashboards, reports and financial KPIs specified and demanded by management.

This post goes through the method, column by column, following the video above. The Power BI PBIX and the UDM Microsoft Excel file used here are a free download, with demo data in them, so the dashboard opens and runs before you point it at your own company.

What is in the download

The Microsoft Excel workbook contains the finished UDM, a Backup_UDM copy of it, instructions on how to use the UDM, and demo data in three SYSPRO tables: GenTransaction (a summary of the transactions), GenMaster and GenGroups. An optional UDM Input sheet builds a UDM from a list of Sections, GL groups and accounts, ready to copy and paste into the UDM sheet.

The Power BI PBIX contains the Profit and Loss dashboard driven by the UDM, using our demo data: KPI cards at the top, a matrix with a level slicer for Level 1, 2 and 3, and a second page with a waterfall and a bar chart.

Why the sort order has to come from somewhere

An ERP chart of accounts, with account codes and account groups, typically has a rough sort order by design, but it is not grouped the way a Profit and Loss or income statement requires.

The UDM is one Microsoft Excel worksheet that contains the layout, custom built with GenGroups and GenMaster as the base, allowing users to then design the layout by adding custom line order, headings, subtotals, totals, ratios and other formatting.

What the UDM controls

• Section headings and GL group hierarchy
• Custom wording for every heading and total
• Blank spacer lines
• Group totals and Section totals
• Custom main revenue and expense totals such as Gross Profit, EBITDA and Profit After Tax
• Ratio and percentage lines
• Display signs and row colours
• Three levels of detail, from Section totals down to individual GL accounts
• Lines left out of the Profit and Loss completely

Sections come first

Add a header row for each Section, RowType Header. L1_Order is the Section number, and it is user defined: 1 Revenue, 2 Cost of Sales, 3 Other Income, 4 Operating Expenses, 5 Depreciation, 6 Finance Costs, 7 Taxation. There can be more or fewer Sections, depending on the layout of your Profit and Loss. The group subtotals, Section totals and Profit and Loss subtotals are all calculated from these numbers.

L1_Name is the Section name, a label only. GlDescription is the text displayed in the matrix. Both are free text, so Revenue can be called Sales or Total Sales, in upper or lower case.

RowOrder is always =ROW()-1. The position of a row in the worksheet is the order it is shown in the matrix, which means a line is moved by moving the row.

GL groups, then accounts

Under each Section, add a header row for each GL group from GenGroups. L2_Group is the GenGroups code, for example 500 or 560, and group subtotals use it to find the accounts that belong to the group. L2_Name is the GenGroups description, and L2_Order is the group order within the Section. A Section with only one GL group, such as Depreciation or Finance Costs, needs no group heading or group subtotal.

Under each group, add the GL Account Codes from GenMaster, RowType Account, the most detailed level of the Profit and Loss. GlDescription joins the GlCode and the account description, so the account lines are easy to recognise, and LineKey is the account code.

Totals without a single minus sign

Every total is a RowType Subtotal row. SumFromL1 and SumToL1 name the range of Sections a total adds up, as L1_Order numbers. A Section total covers one Section, so both columns carry the same number, and SumL2Group is left blank, which includes every group in that Section and picks up a group added later without any change. A group subtotal narrows the same range to one GL group through SumL2Group: Total Cost of Sales - Materials is Section 2 to 2, SumL2Group 200.

LineKey names each total. A group subtotal uses the GL group code, so the subtotal for group 500 has LineKey 500. A Section total uses a short name such as REV, COS or OPEX, and the ratio lines refer to these names. IsBase marks the one line that percentages are calculated against, normally Total Revenue.

Profit and Loss subtotals add up a range of Sections, always starting from Section 1. Gross Profit is Sections 1 to 2. EBITDA is 1 to 4. Operating Profit is 1 to 5, Profit Before Tax 1 to 6, and Profit After Tax 1 to 7, which is every account in the Profit and Loss.

InvertSign then handles presentation. Put -1 on RowType Account to invert the sign of the source GenTransaction data, and leave 1 on Header and Blank rows unless you have specified otherwise. Subtotals also carry -1 to invert the sign.

Spacer lines, ratios and colours

A row with RowType Blank is displayed as an empty line, which separates one Section from the next. A RowType Calc row calculates one line against another: RatioNum and RatioDen are the LineKeys of the two lines, RatioOp is the operator (divide, multiply, plus or minus), and RatioFormat sets whether the result is shown as a Percent, a Value or a Decimal. Gross Profit % of Revenue is RatioNum GP, RatioOp /, RatioDen REV.

Colour is a cell edit rather than a DAX edit. RowFill and RowFont hold hex codes per row, and the measures pass them through to the matrix, so shading a total line is done in Excel. The colours apply to the value columns only, not the row labels, which is a limitation of the Power BI matrix visual.

Three levels of detail

ShowFromLevel sets the level from which a line is displayed. 1 shows at every level, 2 at levels 2 and 3, and 3 only in full detail. The level slicer switches the matrix between them, so one matrix serves the summary a board pack needs and the account detail an accountant needs, without a second report.

ShowLine set to n leaves a line out of the Profit and Loss completely.

Designed for SYSPRO, adaptable to any ERP system

The demonstration tables use the corresponding SYSPRO names, GenTransaction, GenMaster and GenGroups. If you are using Sage, SAP or any similar ERP system, replace the three source tables, General Ledger transactions, chart of accounts and account groups, with equivalents from your own system, keep the same UDM columns, and the Profit and Loss is built the same way.

SYSPRO® is a registered trademark of SYSPRO Proprietary Limited. Microsoft, Power BI and Excel are trademarks of the Microsoft group of companies. ForesightSA is an independent software vendor and is not affiliated with, endorsed by or sponsored by SYSPRO Proprietary Limited.