Power BI visual · Quick start
P&L Statement byDatenWGBeta
A standalone Power BI custom visual for the IBCS-inspired income statement: the account hierarchy comes from your dimension table, with subtotal and formula rows, scenarios AC · PY · PL · FC, variance bars and Δ% pins — in four views from a single field binding: table, structure bars, row waterfall and a value driver tree with card zoom. A click sets the Power BI selection (cross-filtering and native drillthrough), periods are parsed from “YYYY-MM”, date columns or month names, and type sizes follow a preset from HD to UHD.
Beta · version 0.17.0.0
4 views
Format pane DE · EN
IBCS-inspired
📊
Download the visual
Version 0.17.0.0 · .pbiviz · beta
Download straight from the repository and import it in Power BI Desktop via Visualizations → ⋯ → Import a visual from a file.
Get the .pbiviz ↓
The
dist folder always holds exactly the newest build — when the version number moves on, that folder is the reliable source.
Beta notice: this is a beta (0.x) and it is not listed on Microsoft AppSource — so it is not certified by Microsoft either.
Feature scope, field roles and format settings may still change, as may the format of the persisted state. Please try it in a test report first; using it in production reports is at your own risk. Feedback and bug reports are explicitly welcome — via
GitHub issues or at
michael.tenner84@gmail.com.
01Two ways in
You do not have to model a P&L structure to get started. Path 1 binds only the levels of a hierarchy you already have, plus the measures — two minutes. Path 2 adds the fields that turn that hierarchy into a real income statement: row types, formula rows, sign conventions and comments.
Path 1 · 2 minutes
A plain hierarchy
For any dimension hierarchy you already have — say Category → Product line, or Region → Country → Site. No row types, no formulas.
Fields to bind
- Level columns (L1..Ln)Your levels in the order you drop them in — e.g.
Category first, then Product line (max. 8).
- Account ID (key)The unique key of the row. When it is bound, drillthrough hangs off it.
- Sort orderAn integer column controlling the order inside a level.
- Period (month)The month column from the fact table — it feeds monthly columns, sparklines and YTD.
- AC (actual)The one required measure. PY, PL and FC are optional and unlock the variance columns.
- PY · PL · FCPrevious year, plan and forecast as measures — every bound scenario shows up in the legend and the presets.
What you get: a table with automatic subtotals per level, structure bars, a row waterfall — and a value driver tree that grows straight out of the hierarchy without any formula row: with exactly one root that root is the tree root, with several roots a virtual total root (“Total”) carries them as children. Clicking a card chart opens the card zoom with combo chart and scenario grid, and the “↗ Drill” button sets the selection and opens the context menu in one gesture.
Path 2 · the full P&L
P&L with formula rows
Everything from path 1 — plus the columns of your account dimension that carry structure and signs. Only then does the visual compute gross profit, EBITDA, margins and the full-year outlook.
Additional fields
- Row type
Account · Subtotal · Formula · KPI · Separator. Subtotals implied by the levels are created synthetically anyway.
- Formula definitionReferences to other rows by name, e.g.
[Gross profit]+[Operating expenses] or [EBITDA]/[Net revenue]; + − × ÷ and parentheses, formula on formula allowed.
- Sign convention (±1)
1 for revenue, -1 for cost — the sign with which the row enters every total.
- Display invert
true on cost rows: they compute negative but are shown positive.
- Variance invert
true wherever “below plan” is good — cost and taxes.
- CommentFree text per account → a numbered marker on the row and a footnote below the table.
- FC full year · PL full yearFull-year scalars for the FY outlook block (hatched variance bars).
What you get: the complete income statement with result rows and margins, three period blocks (MTD · YTD · FY outlook), a % of revenue column, comment footnotes — and a driver tree grown from the formula graph (DuPont): the root is the last formula/KPI row, and the edges carry the operators ÷ × + −.
The one setting most people get caught by
Every grouping field — level columns, account ID, sort order, row type, formula definition, sign, period — must be set to
“Don't summarize” in the field well. Otherwise Power BI aggregates it and the visual receives empty values.
- Full-year measures with
MAX, not SUM: the FY scalars repeat identically on every monthly row — summed, they come out too large by the number of months.
- Filter to one reporting year, otherwise AC/PY/PL/FC add up across years.
02What the account dimension looks like
One row per P&L line. The excerpt shows exactly the columns from path 2 — levels, sort order, row type, formula and sign.
An excerpt from the pharma demo in the repository. Net revenue and Cost of goods sold are not rows of their own — those subtotals are created synthetically from the L1 paths, and the formula rows reference them by name.
Demo data to play with
The full pharma demo (a fictional group, star schema L1–L3, monthly grain, values in mEUR, YTD 2026-01…2026-06) lives in the repository under
pnlByDatenWG/demoData/pharma/:
Both CSVs are semicolon-separated (UTF-8). Relationship:
pharma-dim-konten[AccountID] 1:*
pharma-fact-guv[AccountID]. Measures:
AC = SUM('pharma-fact-guv'[AC]), likewise PY and PL;
FC FY = MAX('pharma-fact-guv'[FC_FY]) and
PL FY = MAX('pharma-fact-guv'[PL_FY]). To reconcile: EBITDA YTD AC =
571.2 · PY 499.6 · PL 532.0 mEUR.
03Hierarchy and periods — the rules
Two places where real models like to deviate from the textbook: unevenly deep hierarchies and period labels beyond “YYYY-MM”.
Ragged: branches of different depth
The visual knows both common conventions and needs no special handling in the model:
- Empty deeper levels — if
L3 is blank, the last filled level is the leaf.
- A repeated level — if
L2 holds the same text as L1, the hierarchy ends there.
- Rows sharing a path are aggregated; aggregate rows (e.g. PY only on L1) act as a scenario fallback.
- Instead of level columns you can use parent-child via account ID + parent account ID — arbitrarily deep and unbalanced; orphans land visibly in “Not assigned” rather than disappearing quietly. If both are bound, parent-child wins.
Period formats
Parsed are
YYYY-MM,
YYYY-MM-DD, ISO timestamps,
YYYY/MM, real date columns, German and English month names (long and short, with and without a dot: “Dec”, “Dec.”, “December”, “Dez”, “Mrz”, “Sept.”) also combined with a year (“Mar 2026”), plus plain numbers 1–12. Anything unreadable is reported as unreadable instead of guessed.
- Fiscal year? Set Columns → Period sort order to “Data order (fiscal year)” in the format pane: the order of first appearance in the model then wins — ideal for period labels with their own sort column.
- Automatic (default) sorts by calendar as soon as every label is readable; Calendar year forces Jan..Dec.
04The four views
One field binding, four points of view — switched in the toolbar above the statement, without touching the format pane. Every toolbar state is persisted and bookmarkable.
View 1
Table
The classic statement: accounts and result rows with period blocks MTD · YTD · FY outlook, variance bars and Δ% pins against the chosen reference. The variance scale is identical across all rows of a column; margin rows compute in percentage points.
View 2
Structure bars
The same rows, each with a size bar — the structure of the result becomes visible at a glance without giving up the numbers. Built for the question “what is this P&L actually made of”.
View 3
Row waterfall
The statement as a cascade: the result anchors (net revenue, gross profit, EBITDA, EBIT, EBT, net income) sit on the zero line while the cost blocks float between them. The waterfall works on the root level of the hierarchy.
View 4
Value driver tree
The result tree — from the formula graph (DuPont) or, when no formula row is bound, from the plain hierarchy. Tidy layout with one column per level, operator circles on the edges, status borders and compact cards from level 4 on. ⌖ re-roots the tree on any card, the breadcrumb leads back.
Detail
Card zoom
Clicking a card chart in the tree zooms that node to a full page: a combo chart with months and bridge, the scenario grid, “contributes to” and “driven by”. The zoom scales itself into the viewport; “← back to the tree” leaves it.
Detail
Selection & drillthrough
A left click on a value column (or on the tile header) sets the Power BI selection of that row — including every source row of the account; subtotals select the leaves beneath them, formula rows the leaves of their operands. A right click opens the native context menu with the page's drillthrough targets. Switch it off under Style → selection & context menu active.
Setting up drillthrough — three steps
- 1 · Target page: add a page (e.g. “Account detail”) and, under Visualizations → Add drill-through fields here, drop in exactly the column that is bound as account ID (key) in the visual. Without a bound account ID it is the first level column (
L1).
- 2 · Button: on the source page use Insert → Buttons → Blank, then Action → On, Type = Drill through, Destination = Account detail.
- 3 · Use it: click a row in the visual — the button goes live. Or right-click straight away, or press “↗ Drill” in the card zoom.
05Format pane — what is worth a look
Most of it is set from the in-visual toolbar (view · column preset · Δ reference · periods · unit · density · levels · options). These format settings define the defaults behind it — plus a few things the toolbar cannot do.
- Columns → default column preset — which value columns appear on open (AC·PY·PL·FC through ΔPY%·ΔPL%).
- Columns → default Δ reference — auto (PL, else PY), PY, PL or FC.
- Columns → period sort order — automatic · data order (fiscal year) · calendar year.
- Columns → % of revenue + revenue base row — empty means “Net revenue”; any other row works too.
- Columns → hide zero rows and fit table to width.
- Columns → driver tree root row — id, name or category; plus start depth, card chart (months · Δ · mini bridge) and status indicator.
- Numbers → scaling, unit, decimals — auto/k/m, unit text, separate decimals for values and Δ%.
- Style → variance colors — teal deviation (safe for red–green color-vision deficiency, the default) or IBCS classic green.
- Style → font size preset — HD · Full HD · UHD, plus fine scaling 80–160 %; it applies to every label.
- Style → sticky header — header rows stay put while scrolling.
- Style → selection & context menu active — cross-filtering and drillthrough on or off.
- Title block — three IBCS lines (reporting entity · measure + unit · period + scenarios) plus an optional message line.
- Hierarchy — default expand level and indent per level.
- Toolbar — hide it entirely or switch off individual groups; the settings keep working.