Report lines from the data model using JSON - Profitbase/PowerBI-visuals-FinancialReportingMatrix GitHub Wiki

Define the rows of your financial statement in a table in your data model instead of building them one by one in Edit mode. Every report line (its label, its formula, its style, its number format and its sign) is stored as a small piece of JSON text. The visual reads that text and builds the row.

This feature is free. It does not need a Premium license.

An income statement built from a Report Layout table

Prefer video? Watch Income Statement – Ready in 1 minute with embedded formulas, formats and styling using JSON.

When to use it

Use report lines from the data model when:

The same statement is used in many reports. Change the table once and every report that uses it updates.
The structure changes often. Adding a new line is a new table row, not an Edit mode session in every .pbix file.
You want the report definition under version control. A table can live in SQL, a lakehouse, Excel or a CSV file, where you can review and compare it.
You have several statements. Keep the income statement, balance sheet and cash flow in one table and switch between them with a slicer.

Use Edit mode instead for things that belong to one report only, such as a single conditional formatting rule or a pinned column. You can combine both: Edit mode steps run on top of the JSON rows.

How it works

When a row label in the Rows bucket starts with {, the visual reads the label as JSON. It does not show the text. It uses these keys instead:

Key	What it does	Example
`id`	The row's identifier. Formulas in other rows refer to the row by this id.	`"IS03"`
`displayName`	The label shown in the row header.	`"Total Operating Income"`
`formula`	A calculation that fills the row from other rows. Leave it empty for rows that get their numbers from the data.	`"IS01+IS02"`
`style`	One or more row styles, separated by spaces.	`"stylesSubtotal"`
`formatString`	The number format for the row.	`"#,0.0%"`
`signFactor`	`1` keeps the sign, `-1` flips it.	`-1`
any other key	Your own data. Read it in formulas and conditions with `Row().yourKey`.	`"IsIncome": 1`

A row from the data (no formula):

{"id":"IS01","displayName":"Sales","formula":"","style":"","formatString":"#,0","signFactor":-1,"IsIncome":1}

A subtotal row:

{"id":"IS03","displayName":"Total Operating Income","formula":"IS01+IS02","style":"stylesSubtotal","formatString":"#,0","signFactor":1,"IsIncome":0}

A KPI row:

{"id":"IS09A","displayName":"Operating Margin","formula":"IS09/IS03","style":"stylesKPI","formatString":"#,0.0%","signFactor":1,"IsIncome":0}

Step 1: Build a Report Layout table

Create a table with one row per report line. The income statement in the screenshots uses this layout:

ReportID	Report	id	displayName	formula	style	formatString	signFactor	IsIncome	SortOrder
IS	Income statement	IS01	Sales			`#,0`	-1	1	10
IS	Income statement	IS02	Other Operational Income			`#,0`	-1	1	20
IS	Income statement	IS03	Total Operating Income	`IS01+IS02`	`stylesSubtotal`	`#,0`	1	0	30
IS	Income statement	IS04	Cost of Goods			`#,0`	1	0	40
IS	Income statement	IS04B	Change in stock WIP FG			`#,0`	1	0	50
IS	Income statement	IS05	Payroll and social Cost			`#,0`	1	0	60
IS	Income statement	IS07	Other Operating Expenses			`#,0`	1	0	70
IS	Income statement	IS08	Total Operating Expenses	`IS04+IS04B+IS05+IS07`	`stylesSubtotal`	`#,0`	1	0	80
IS	Income statement	IS09	Operating Profit	`IS03-IS08`	`stylesSubtotal`	`#,0`	1	0	90
IS	Income statement	IS09A	Operating Margin	`IS09/IS03`	`stylesKPI`	`#,0.0%`	1	0	100
…
IS	Income statement	IS21	Net profit loss	`IS16-IS17`	`stylesTotal`	`#,0`	1	0	200

The Report Layout table in Power BI's Table view

IsIncome is an example of your own key, see Your own keys. Leave it out if you don't need it.

Relate the table to your chart of accounts so that each account belongs to one data row. In the example, the Account table has a ReportLine column that holds the line id (every sales account holds IS01):

Report Layout[id] (one) → Account[ReportLine] (many)
Account[Account] → your fact tables (actuals, budget)

Subtotal and KPI rows such as IS03 and IS09A have no accounts. Their numbers come from the formula.

Model view: Report Layout[id] related to Account[ReportLine], and Account to the fact tables

Step 2: Create the ReportLineJSON column

Add a calculated column to the Report Layout table that combines the columns into one JSON text:

ReportLineJSON =
"{""id"":""" & 'Report Layout'[id] & """"
& ",""displayName"":""" & 'Report Layout'[displayName] & """"
& ",""formula"":""" & 'Report Layout'[formula] & """"
& ",""style"":""" & 'Report Layout'[style] & """"
& ",""formatString"":""" & 'Report Layout'[formatString] & """"
& ",""signFactor"":" & COALESCE ( 'Report Layout'[signFactor], 1 )
& ",""IsIncome"":" & COALESCE ( 'Report Layout'[IsIncome], 0 )
& "}"

Replace 'Report Layout' and the column names if yours are different. Remove the IsIncome line if you don't use that key, or copy it to add your own keys.

You can also build the JSON text in Power Query, Excel or the source database. The result must be valid JSON, and the text must start with { with no space in front of it.

⚠️ Watch out for quotes. A " inside a label (for example Sales "Nordic") breaks the JSON. Remove it, or escape it as \".

⚠️ Never leave a number empty. "signFactor":} is not valid JSON. That is why the formula wraps the numbers in COALESCE.

The ReportLineJSON column, with its DAX in the formula bar

Step 3: Sort the lines in statement order

By default, Power BI sorts the JSON text alphabetically. Set a fixed order:

Select the ReportLineJSON column.
Choose Column tools → Sort by column → SortOrder.

Column tools: Sort by column set to SortOrder

Step 4: Add the field to the visual

Select the Financial Reporting Matrix on the canvas.
Drag ReportLineJSON into Rows as the first (top) field. You can add more detailed fields under it, for example Account Description.
Add your measures to Values and your period fields to Columns as usual.
In the Rows bucket, right-click ReportLineJSON and turn on Show items with no data.

ReportLineJSON first in Rows, with Show items with no data ticked

Step 4 matters for every line that has no accounts: all subtotal and KPI rows, and data rows that no account points to. Power BI has no data for those lines and leaves them out unless Show items with no data is on. In the example, 10 of the 20 lines disappear without it:

Show items with no data Off: the subtotals, Operating Margin and the lines without accounts are missing

Show items with no data On: all 20 lines appear

⚠️ The JSON field must be the top level in Rows. The visual only finds a row by its plain id when the JSON row is at the top level. If you put another field above ReportLineJSON, formulas such as IS01+IS02 stop finding their rows.

Step 5: Show one statement at a time

When the table holds more than one statement, the visual shows the lines of all of them together. Add a slicer on ReportID or Report (or filter the visual on it), so the visual gets the lines of one statement at a time.

A slicer with Income statement selected

The same slicer with Balance sheet selected

Writing formulas

Refer to other rows by their id. IS01+IS02 adds the two rows, in every column.
Use the four arithmetic operators: +, -, * and /. Use brackets to control the order: (IS01+IS02)/IS03.
Ids must be simple names. Start with a letter and use only letters, digits and _. An id such as IS-01 is read as IS minus 01, and 1000 is read as a number.
Ids must be unique across the whole table.
Formulas see the signed values. The signFactor of every row is applied first, so IS03-IS08 uses Sales as already flipped.
Dividing by zero gives 0, not an error.
A formula row can use another formula row, for example IS09 = IS03-IS08. Put the row it uses above it.

Styles

Use any of these names in the style key. Combine several names with a space, for example "stylesSubtotal overline".

Name	Format pane card
`stylesTotal`	Style - Total
`stylesSubtotal`	Style - Subtotal
`stylesKPI`	Style - KPI
`bold`	Style - Bold
`overline`	Style - Overline
`underline`	Style - Underline
`custom1` … `custom6`	Style - Custom1 … Custom6
`hidden`	Hides the row

Set up how each style looks in its card in the Format pane, or in your report theme. Total, Subtotal and KPI have a default look (bold, bold and italic). custom1 to custom6 change nothing until you set up their card.

In the example, Style - Subtotal is bold with an outline on top:

Style - Subtotal in the Format pane, and the Total Operating Income row that uses it

Number formats

formatString takes a Power BI format string, for example #,0, #,0.00 or #,0.0%.

The row's format wins over the Column styles format and the default format string in Values. It loses to formats you set in Edit mode (Update row style, Add row, Add measure) and to the Format() function in a column formula.

Your own keys

Add any key to the JSON and read it back with Row().yourKey. The example adds IsIncome, which is 1 on the five income lines and 0 on all others:

{"id":"IS01","displayName":"Sales","formula":"","style":"","formatString":"#,0","signFactor":-1,"IsIncome":1}

To color the actual values of the income lines:

In the Format pane, open Style - Custom1 and pick a background color.
Enter Edit mode, right-click the Actual column header and choose Add conditional formatting.
Under Conditions, set the first condition to Actual and unimportant. It then puts no limit on the values.
Choose AND, and type Row().IsIncome == 1 in the condition field below it.
Under Apply style, keep Default and choose custom1. Select Apply.

The income lines are now teal in every Actual column. The rule shows up as CF (Actual) in Applied steps, where you can edit or delete it.

A conditional formatting rule using Row().IsIncome == 1: only the income lines are colored

You can use the same condition in Hide empty rows → Condition, and in column formulas.

Working with other features

Feature	How it works with JSON rows
Hide empty rows	Always show JSON rows (on by default) keeps empty JSON rows visible. Turn it off to hide them as well. See Hide empty rows.
Conditions	`IsJSONRow()` returns `true` for rows that come from JSON. Use it in a condition to style or hide only those rows.
Edit mode	Applied steps (row styles, custom rows, conditional formatting) run after the JSON rows are built, so they can change or override them.
Set sign factor	A sign factor set in Edit mode is applied on top of the JSON `signFactor`.
Custom subtotal rows	You can still add rows before or after a JSON row in Edit mode.

Troubleshooting

What you see	Likely cause	What to do
The row header shows the raw `{"id": …}` text.	The text does not start with `{`, or the label is not a text column.	Remove spaces in front of `{`. Make sure the column type is Text.
The visual stops rendering after you add the field.	One of the labels is not valid JSON.	Check the rows for quotes in labels, empty numbers or a missing `}`. Paste one value into a JSON validator.
A subtotal, KPI or data line is missing.	Power BI sends no data for lines that no account points to.	Turn on Show items with no data for ReportLineJSON in Rows.
A row with an empty label holds values.	Some accounts point to a line id that is not in the Report Layout table, or the visual shows lines from another statement.	Add the missing ids to the table, or filter the visual on ReportID.
Lines from two statements appear together.	The visual is not filtered on one statement.	Add a slicer or a visual filter on ReportID or Report.
A formula row shows `-` or `0`.	An id in the formula does not match any row, or the JSON field is not the top level in Rows.	Check the spelling and case of each id. Move ReportLineJSON to the top of Rows.
Lines appear in the wrong order.	Power BI sorts the JSON text alphabetically.	Use Sort by column with a sort-order column.
A style does nothing.	The style card is not set up.	Set up the matching Style - … card in the Format pane.