P&L STATEMENT

G U I D E

Custom Visual: HTLM VizCreator Cert
Main features used:
HTML VizCreator driven by DAX measures to render the statement as a single HTML table
Formatting table metadata to control indentation, emphasis, colors, and row behavior.
SWITCH based DAX calculations to manage totals, subtotals, and mixed numeric formats

You can download the .pbix here

Introduction and Motivation
High Level Overview of the Solution
Step I – Data Model Design
Step I – Core Measures and Totals
Step I – Rendering Rows with HTML
Step I: Header and Dynamic Context
Step II: Improving Visual Aesthetics
Step II: Cleaner HTML and Styling Patterns
Step III: Adding More Sub Levels
Step III: Scaling the Pattern and Final Outcome

Why this use case?

Power BI native visuals struggle with complex financial layouts. Tables and Matrix visuals can show a P and L, but they impose structural limits that conflict with how financial statements are designed.

This use case focuses on building a Profit and Loss statement that follows accounting logic, not visual constraints.

A standard Table or Matrix can list accounts and totals, but it becomes fragile when you add indentation, custom subtotals, mixed currency and percentage rows, or visual separators. These patterns often require heavy conditional formatting and complex DAX that is hard to maintain.

A financial statement is read as a structured document. Spacing, grouping, and emphasis matter as much as numbers.

HTML VizCreator fits this scenario. It renders a P and L as a controlled layout, keeps calculations in DAX, and lets Power BI handle filters and interaction.

#1 Introduction

This guide explains how to build a pixel perfect P and L statement in Power BI using HTML VizCreator.

The final result is a single P and L table with clear indentation, line level hints, and mixed formatting in the same column. Currency values, percentages, headers, and subtotals live together without forcing extra columns or visuals. The layout follows the logic of a financial statement, not the constraints of a generic visual.

Why native visuals fall short
A Table or Matrix visual can show a P and L, but pushing them to this level usually requires DAX workarounds that are hard to maintain.
Common limitations include:

Limited row level formatting. Bold text, underlines, borders, and indentation often rely on heavy conditional formatting rules.
Fragile subtotal logic. Non standard rows such as Gross Margin percent, Operating Margin percent, or EBITDA with custom spacing tend to break when the layout changes.
Mixed formatting issues. Showing currency and percentage values together often forces multiple measures or formatting compromises.

These patterns work, but they leave technical debt behind.

Why not Paginated Reports
Paginated Reports solve layout and formatting, but they come with tradeoffs.
They run in a different authoring environment and follow a different development workflow. They also do not offer the same interactive canvas experience as a Power BI report page with slicers and cross filtering.
This guide focuses on staying inside Power BI reports while gaining finer control over layout.

What you will learn
You will learn how to use HTML VizCreator as a general HTML rendering engine inside Power BI.
The approach separates statement structure from data. DAX builds the logic. HTML controls the layout. Power BI handles filtering and interaction.

Skills required
You need basic Power BI skills and basic DAX. No advanced modeling is required.
The guide introduces a small amount of HTML. Only what is necessary to understand how the statement is rendered.

Guide Structure
The guide is split into three sections:

Step I: Build the core data structure and the measures that generate the P and L.
Step II: Improve spacing, aesthetics, and visual clarity.
Step III: Add more rows, deeper levels, and more complex statement logic.

Each section builds on the previous one and keeps the focus on decisions you need to make when designing a financial statement in Power BI.

#2 High Level Overview

HTML VizCreator renders HTML that comes from a Power BI measure. The measure returns a single text value that contains valid HTML. HTML VizCreator displays that HTML inside the visual.
This moves some work from visual formatting panes to the model. DAX defines what to show. HTML defines how it looks.

What HTML means here
HTML is the standard language used to structure content on the web. It defines elements such as text blocks, containers, and inline elements.
In this guide, HTML is used only for layout and formatting. It does not handle data logic, calculations, or interaction. All values still come from DAX measures and respect the Power BI filter context, so slicers and filters behave as expected.

How this applies to a P and L statement 
The P and L is rendered as a HTML table inside HTML VizCreator visual.
The measure reads the formatting data from a Formatting Table (introduced on the next page). That table defines row level rules such as indentation, separators, and emphasis.
The measure logic decides:

which line items appear
the indentation level for each row
formatting rules such as bold text and separator lines
whether a hint is shown for a line item

HTML converts those rules into spacing, alignment, and text styling.

Guide flow
The guide is split into three sections:

Step I
Build the minimum structure: a simple P and L with a few rows. Review the data model pattern: one table for values, one table for formatting.
Step II
Improve readability by updating the Formatting Table and adding a few HTML elements to refine the layout.
Step III
Extend the statement with more rows, deeper levels, subtotals, and mixed formats without changing the visual

Data model + starting measures

Data model
(2 tables)

6 measures
(3 with HTML)

Pixel-perfect
table
Aesthetics improvement

Formatting table
improvement

HTML code
for aesthetics
More sub-levels

More rows into
P&L Statement

#3 STEP I: Data model

In this step, we build the bare bones of the solution: a pixel perfect P and L statement that focuses on structure and logic, not appearance.
The goal here is to make the statement work. Layout refinements and visual polish come later, in Step II. For now, the focus stays on understanding the data model and the measures that drive the result.
By the end of this step, you will have a working P and L rendered in HTML VizCreator with correct totals, row order, and indentation. The output is intentionally simple.
This is the table built in this step:

This solution requires only two tables.
The first table stores the P and L values. The second table defines the structure and formatting rules of the P and L statement.
Table 1: P&L Data

Table 2: P&L Formatting

Account ID
Both tables are related through Account ID.
An account represents a type of financial line item, such as Sales of Products, Sales of Services, Operating Expenses, or Interest Expense. Each account gets a unique identifier. That identifier is the Account ID.
The Account ID does not describe hierarchy by itself. It is a stable key that lets you link values, totals, and formatting rules to the same line item.

P and L Data table
The P and L Data table contains the actual values that will be rendered in the statement. This table is typically long because it holds data by date, scenario, or other dimensions.
At this step, you only need the lowest level of detail. For example:
Sales of Products, Account ID 1
Sales of Services, Account ID 2
There is no row for Total Revenue yet. Total Revenue, Account ID 3, will be created later in the measure by summing Account ID 1 and 2.
The same pattern applies to Expenses and Interest. Store base accounts only. Build totals and subtotals in DAX.

P and L Formatting table
The P and L Format table contains one row per line item in the final statement. It does not store values. Instead, it defines presentation rules such as:

background color.
indentation level
underline or separator flags
visibility and hint text

In this use case, the label text (for example “Train A”, “Train B”) is hard coded This table controls how each row looks. Updating the statement becomes a data change, not a visual configuration task.
This separation keeps the model clean and makes the P and L easier to extend and maintain.

#4 STEP I: Measures

Measures
The solution uses six measures to keep calculations separate from layout.
Measure: % of Turnover
Returns each line item as a share of Total Revenue by dividing the current P and L value by Total Revenue. This simple measure is mainly used later for percentage based rows such as margins.

% of Turnover =
VAR var_value = 
    SUM ( 'P&L Data'[Amount] )
VAR var_total_revenue = Total revenue equals the sum of Account ID 1 (Product Sales) and Account ID 2 (Service Sales)
    CALCULATE (
        SUM ( 'P&L Data'[Amount] ),
        REMOVEFILTERS ( 'P&L Format' ),
        'P&L Format'[Account ID] in {1,2}
    )
VAR var_result = 
    DIVIDE ( var_value, var_total_revenue, 0 )
RETURN
    FORMAT ( var_result, "(0.0%)" )

Measure: P&L Value
Is the main measure for actuals. It uses the Account ID from the P and L Format table with a SWITCH pattern: base accounts return the summed values from the P and L Data table, while calculated rows (like Total Revenue or Total Expenses) explicitly sum selected Account IDs. This keeps totals consistent and not dependent on how the visual aggregates data.

P&L Value =
VAR var_account_id = SELECTEDVALUE ( 'P&L Format'[Account ID] )
VAR var_value = SUM ( 'P&L Data'[Amount] )
VAR var_total_revenue =
    CALCULATE (
        SUM ( 'P&L Data'[Amount] ),
        REMOVEFILTERS ( 'P&L Format' ),
        'P&L Format'[Account ID] IN { 1, 2 }
    )
VAR var_total_expenses = Total expenses equals the sum of Account ID 4 
(Employee Expenses), 5 (Cost of material), and 6 
(Other Expenses)
    CALCULATE (
        SUM ( 'P&L Data'[Amount] ),
        REMOVEFILTERS ( 'P&L Format' ),
        'P&L Format'[Account ID] IN { 4, 5, 6 } )

VAR var_EBIT = var_total_revenue + var_total_expenses
VAR var_result =
    var_EBIT
    + CALCULATE (
        SUM ( 'P&L Data'[Amount] ),
        REMOVEFILTERS ( 'P&L Format' ),
        'P&L Format'[Account ID] = 10 )

VAR var_ebit_of_total_revenue = FORMAT ( DIVIDE ( var_EBIT, var_total_revenue, 0 ), 
"0.0%" )
VAR var_amount =         The displayed metric dynamically adjusts based 
on the line item shown in the final table
    SWITCH (
        var_account_id,
        3, var_total_revenue,
        7, var_total_expenses,
        8, var_EBIT,
        9, var_ebit_of_total_revenue,
        11, var_result,
        var_value)
RETURN
    var_amount

Measure: Plan Value
This measure mirrors P&L Value but points to plan or budget data instead of actuals.
The structure stays the same. Only the underlying column or filter changes. Keeping both measures aligned avoids inconsistencies between Actual and Plan rows.

Plan Value =
VAR var_account_id = SELECTEDVALUE ( 'P&L Format'[Account ID] )
VAR var_value = SUM ( 'P&L Data'[Plan] )
VAR var_total_revenue =
    CALCULATE (
        SUM ( 'P&L Data'[Plan] )
        ,
        REMOVEFILTERS ( 'P&L Format' ),
        'P&L Format'[Account ID] IN { 1, 2 })
VAR var_total_expenses =
    CALCULATE (
        SUM ( 'P&L Data'[Plan] )
        ,
        REMOVEFILTERS ( 'P&L Format' ),
        'P&L Format'[Account ID] IN { 4, 5, 6 })
VAR var_EBIT = var_total_revenue + var_total_expenses
VAR var_result =
    var_EBIT
    + CALCULATE (
        SUM ( 'P&L Data'[Plan] )
        ,
        REMOVEFILTERS ( 'P&L Format' ),
        'P&L Format'[Account ID] = 10)
VAR var_ebit_of_total_revenue = FORMAT ( DIVIDE ( var_EBIT, var_total_revenue, 0 
), "0.0%" )
VAR var_Plan =
    SWITCH (
        var_account_id,
        3, var_total_revenue,
        7, var_total_expenses,
        8, var_EBIT,
        9, var_ebit_of_total_revenue,

#5 STEP I: Measures

Measure: P&L
This measure decides what numeric value to show for each row.
Based on the row type defined in the P and L Format table, it returns either:

P&L Value
Plan Value
% of Turnover

This allows currency values and percentages to live in the same column without extra visuals or measures.

P&L =
VAR var_account = SELECTEDVALUE ( 'P&L Format'[Account] )
VAR var_account_id = SELECTEDVALUE ( 'P&L Format'[Account ID] )
VAR var_account_with_percent = 
    IF ( 
        var_account_id < 3,
        var_account & " <b> " & [% of Turnover] & " </b> of Total Revenue",
        var_account
    )

VAR var_account_hint = SELECTEDVALUE ( 'P&L Format'[Account hints] )
VAR var_indent = SELECTEDVALUE ( 'P&L Format'[Indent] ) * 20
VAR var_background = SELECTEDVALUE ( 'P&L Format'[Background Color] )
VAR var_font_color = SELECTEDVALUE ( 'P&L Format'[Font Color] )
VAR var_bold = IF ( SELECTEDVALUE ( 'P&L Format'[Bold] ) = "x", "<strong>", "" )
VAR var_underline = IF ( SELECTEDVALUE ( 'P&L Format'[Underline] ) = "x", "<u>", "" )
)
VAR var_value = [P&L Value]
VAR var_plan = [Plan Value]
RETURN
    "<table style='border-collapse: collapse; width: 100%;' 
border='1'>><colgroup><col style='width: 60%;'><col style='width: 20%;'><col 
style='width: 20%;'></colgroup>" Columns width
        & "<tr style='height: 25px;'>"
        & "<td title='' & var_account_hint & " style='padding-left: "
        & var_indent & "px; background-color: " & var_background & "; color: " &
var_font_color & "; '>"
        & var_bold & var_underline & var_account_with_percent & "</td>"
        & "<td style='padding-right: 10px; text-align: right;background-color: "
        & var_background & "; color: " & var_font_color & "; '>" & FORMAT ( 
var_value, "0,0" ) & "</td>"
        & "<td style='padding-right: 10px; text-align: right;background-color: "
        & var_background & "; color: " & var_font_color & "; '>" & FORMAT ( 
var_plan, "0,0" ) & "</td>"
        & "</tr></table>"

SELECTEDVALUE() identifies the current line item in the row and retrieves the corresponding formatting settings from the ‘P&L Format’.

After the RETURN statement, the measure stops behaving like a numeric calculation and starts acting as a layout definition.
The code returns a single HTML string that defines one row of the P and L table. The <table>, <tr>, and <td> tags create the table structure, column widths, and row height. This ensures consistent alignment for labels, actual values, and plan values.
Each cell is then formatted using values read from the P&L Format table:

padding-left applies indentation based on the Indent column.
background-color and color control row level coloring.
and are conditionally injected to apply bold or underline.
The title attribute injects the account hint, which appears on hover.

The measure also formats numbers and places Actual and Plan values in right aligned columns.
In short, the logic before RETURN decides what the row represents. The HTML after RETURN defines how that row is rendered, using formatting rules stored in the P and L Format table.
Measure: P&L Message
Creates a short, dynamic message that will be shown in the header later through P&L Header measure. It is not placed in the visual by itself.

#6 STEP I: Measures

P&L Message =
VAR var_region = SELECTEDVALUE ( 'P&L Data'[Region], "all regions in total" )
VAR var_value = 
    FORMAT ( 
        CALCULATE ( SUM ( 'P&L Data'[Amount] ), REMOVEFILTERS ( 'P&L Format' ) ), 
        "0,0")
VAR var_variance = 
    var_value 
        - CALCULATE ( SUM ( 'P&L Data'[Plan] ), REMOVEFILTERS ( 'P&L Format' ) )
VAR var_above_below = IF ( var_variance >= 0, "above", "below" )
VAR var_color = IF ( var_variance >= 0, "green", "red" )  Color rule
RETURN
"<p>The Net Result of "
    & var_region
    & " is <strong>"
    & var_value
    & "</strong> which is <span style='color: "
    & var_color
    & ";'><strong>"
    & var_variance
    & " "
    & var_above_below
    & " Plan </strong></span></p>"

The measure reads the selected Region from P&L Data. If no region is selected, it falls back to “all regions in total”. It then calculates the total Amount and formats it for display.
Next, it calculates the variance versus Plan and classifies the result as above or below plan. The same variance also drives a simple color rule: green when variance is zero or positive, red when negative.
The RETURN builds an HTML paragraph using . It states the net result for the current region, shows the value in bold with , and applies the color to the above or below plan
text.

Measure: P&L
Defines the header section of the P and L table rendered by HTML VizCreator. The visual allows the body and the header to be driven by separate measures, which keeps structure and content aligned.
The measure returns an HTML table with three columns. The first column contains the statement title, the currency label, and the output of P&L Message, which adds dynamic context based on the current filters. The second and third columns label the numeric sections as Actuals and Plan.

P&L Header =
RETURN
"<table style='border-collapse: collapse; width: 100%;' border='0'>
    <colgroup>
        <col style='width: 60%;'>
        <col style='width: 20%;'>
        <col style='width: 20%;'> Columns width
    </colgroup>
    <tr style='height: 19px;'>
        <td>
            <strong>Profit & Loss Statement<br></strong>
            in USD<br>
            <span style='font-size: 15px;'>" & [P&L Message] & "</span>
        </td>
        <td style='padding-right: 10px; text-align: right;'>
            <strong>Actuals</strong>
        </td>
        <td style='padding-right: 10px; text-align: right;'>
            <strong>Plan</strong>
        </td>
    </tr>
</table>"

Column widths and text alignment match the body of the P and L, so headers stay perfectly aligned with values. All styling is handled in HTML, keeping the header consistent and independent from visual formatting settings.

Field wells
Finally, place the measures and fields in the correct field wells. ‘P&L’ drives the table rendering, while the ‘Account’ column generates the line item rows. ‘P&L Header’ supplies the visual header.
Row order is controlled by ‘Account ID’, an integer from 1 to 11. The same key also links the two tables in the model.

#7 STEP II: Aesthetics

Congratulations! You have completed Step I. The core structure is now in place, and the P and L statement is already functional.
Step II focuses on appearance. The goal is to give the statement a cleaner, more modern look without changing the overall pattern. This is done in two ways:

Update the formatting rules in the P&L Format table
Add a few more HTML elements to the measures to reflect those rules

New formatting values
In the P&L Format table, several background colors are updated and new ones are introduced.

Even small changes here can noticeably improve readability and visual hierarchy.
Because formatting is driven by table metadata, these updates do not require changes in the visual format pane.

Measures
Three of the six measures will be updated. These are the measures that output HTML. The goal is a cleaner layout: thinner gridlines, more padding, consistent typography, and safer default colors.

Measure: P&L Value
It still reads row metadata from P&L Format using SELECTEDVALUE(): account name, indentation, hints, and style flags. It then builds one HTML row with three columns (label, Actual, Plan).

P&L=
VAR var_account = SELECTEDVALUE ( 'P&L Format'[Account] )
VAR var_account_id = SELECTEDVALUE ( 'P&L Format'[Account ID] )
VAR var_account_with_percent = 
    IF ( 
        var_account_id < 3,
        var_account & " <b> " & [% of Turnover V2] & " </b> of Total Revenue",
        var_account)
VAR var_account_hint = SELECTEDVALUE ( 'P&L Format'[Account hints] )
VAR var_indent = SELECTEDVALUE ( 'P&L Format'[Indent] ) * 20
VAR var_background = 
    COALESCE ( SELECTEDVALUE ( 'P&L Format'[Background Color] ), "#ffffff" )
VAR var_font_color = 
    COALESCE ( SELECTEDVALUE ( 'P&L Format'[Font Color] ), "#4e4e4e" ) New font color
VAR var_bold = IF ( SELECTEDVALUE ( 'P&L Format'[Bold] ) = "x", "<strong>", "" )
VAR var_underline = IF ( SELECTEDVALUE ( 'P&L Format'[Underline] ) = "x", "<u>", "" )
VAR var_value = [P&L Value V2]
VAR var_plan  = [Plan Value V2]

VAR cGrid        = "#e0e0e0"
VAR cColDivider = "#eef2f7"
VAR cFont       = "Segoe UI, Arial, sans-serif"
VAR cFontSize   = "12.5px"
VAR cRowHeight  = "30px" New grid color, divider color, 
font family, font size and higher 
row height (from 25px to 30px)

VAR sCellBase = 
    "font-family:" & cFont & ";" & 
    "font-size:" & cFontSize & ";" & 
    "color:" & var_font_color & ";" & 
    "background-color:" & var_background & ";" & 
    "border-bottom:1px solid " & cGrid & ";" & 
    "vertical-align:middle;"

VAR sCellLeft = 
    sCellBase & 
    "padding:6px 10px;" & More padding
    "padding-left:" & var_indent & "px;" & 
    "border-right:1px solid " & cColDivider & ";"

VAR sCellNum = 
    sCellBase & 
    "padding:6px 10px;" & More padding
    "text-align:right;" & 
    "border-right:1px solid " & cColDivider & ";"

VAR sCellNumLast = 
    sCellBase & 
    "padding:6px 10px;" & More padding
    "text-align:right;"

RETURN
    "<table style='border-collapse:collapse; width:100%; border:0;'>
        <colgroup>
            <col style='width:60%;'>
            <col style='width:20%;'>
            <col style='width:20%;'>
        </colgroup>
        <tr style='height:" & cRowHeight & ";'>
            <td title='" & var_account_hint & "' style='" & sCellLeft & "'> " & 
                var_bold & var_underline & var_account_with_percent & "
            </td>
            <td style='" & sCellNum & "'> " & FORMAT ( var_value, "0,0" ) & " </td>
            <td style='" & sCellNumLast & "'> " & FORMAT ( var_plan, "0,0" ) & " </td>
        </tr>
    </table>"

#8 STEP II: Aesthetics

Compared to Step I, the measure introduces “modern look” constants (font, font size, row height) and centralizes styling into reusable strings (sCellLeft, sCellNum, sCellNumLast). Gridlines switch to subtle 1px borders, vertical dividers get lighter, and padding increases for readability. COALESCE() provides a clean fallback when background or font color is blank in P&L Format.

Measure: P&L Message
The second version of P&L Message keeps the same purpose as before but improves clarity and presentation.
From a logic perspective, the main change is that numeric values and formatted text are separated.
Visually, the message switches from a block to a , so it behaves like inline text inside the header. Colors are updated to softer tones, and directional indicators (▲ / ▼) are added to reinforce above or below plan at a glance. Typography is standardized using the same font family and a smaller font size.

P&L Message 2 =
VAR var_region =
    SELECTEDVALUE ( 'P&L Data'[Region], "all regions in total" )

VAR var_value_num =
    CALCULATE (
        SUM ( 'P&L Data'[Amount] ),
        ALL ( 'P&L Format' )
    )

VAR var_value =
    FORMAT ( var_value_num, "0,0" )

VAR var_variance_num =
    var_value_num
        - CALCULATE (
            SUM ( 'P&L Data'[Plan] ),
            ALL ( 'P&L Format' )
        )

VAR var_variance =
    FORMAT ( var_variance_num, "0,0" )

VAR var_above_below =
    IF ( var_variance_num >= 0, "above ▲", "below ▼" )

VAR var_color =
    IF ( var_variance_num >= 0, "#0d99ff", "#cc5500" )

RETURN
"<span style='font-family:Segoe UI, Arial, sans-serif; font-size:12px; 
color:#696969;'>
    The Net Result of " & var_region & " is <strong>" & var_value & "</strong> 
which is 
    <span style='color:" & var_color & "; font-weight:600;'>" & 
        var_variance & " " & var_above_below & " Plan
    </span>
</span>"

Measure: P&L Header
The second version of P&L Header keeps the same structure as before but refines styling and consistency.

P&L Header =
VAR cHeaderBg   = "#edf2f8"
VAR cHeaderText = "#8c8cb1" New background color and font color
VAR cGrid       = "#e6e9ef"
VAR cFont       = "Segoe UI, Arial, sans-serif"

RETURN
"<table style='border-collapse:collapse; width:100%; border:0; font-family:" & cFont & ";'>
    <colgroup>
        <col style='width:60%;'>
        <col style='width:20%;'>
        <col style='width:20%;'>
    </colgroup>
    <tr style='background-color:" & cHeaderBg & "; color:" & cHeaderText & "; height:34px;'>
        <td style='padding:10px 10px; border-bottom:1px solid " & cGrid & ";'>
            <span style='font-weight:600;'>
                ""Profit & Loss Statement"" &
            </span><br>
            <span style='font-size:12px;'>in USD</span><br>
            <span style='font-size:12px; color:#7d7c7e;'>" & [P&L Message 2] & "</span>
        </td>
        <td style='padding:10px 10px; text-align:right; font-weight:600; border-bottom:1px solid " & cGrid & ";'>
            Actuals
        </td>
        <td style='padding:10px 10px; text-align:right; font-weight:600; border-bottom:1px solid " & cGrid & ";'>
            Plan
        </td>
    </tr>
</table>"

Styling is centralized in variables for background, text, grid, and font, so it is easier to maintain. Padding is larger and the row height is fixed to align with the table body.
The header uses a lighter background and softer text, with only a subtle bottom border instead of heavy gridlines. Font settings match the body for a cleaner, consistent layout.

#9 STEP III: More sub-levels

Final table:

In this step, the 'P&L Format' table is extended with additional rows and deeper hierarchy levels (more “child” line items under revenue and expense groupings). The key design remains unchanged:

The visual formatting stays the same.
All indentation, styling, and grouping behavior continues to be driven by the 'P&L Format' metadata columns and the existing measures.
The measures are largely reusable; the main changes are driven by the expanded chart-of-accounts structure (more IDs to aggregate).

What changes conceptually
Total Revenue and Total Expenses now have additional intermediate line items. The “totals” logic aggregates more Account IDs:

Total Revenue is now composed of:
- Sales of Products (Account IDs 2, 3, 4)
- Sales of Services (Account IDs 6, 7, 8
Other expenses is now a “middle” level subtotal:
- Account IDs 13, 14, 15
Total Expenses becomes the sum of:
- Employee Expenses (ID 10)
- Costs of material (ID 11)
- Other expenses children (IDs 13, 14, 15)

What changes in the measures
% of Turnover measure: update Total Revenue aggregation
The only change is expanding the set of Account IDs included in Total Revenue. The calculation sums the revenue child rows:

VAR var_total_revenue =

CALCULATE (

SUM ( 'P&L Data'[Amount] ),

REMOVEFILTERS ( 'P&L Format' ),

'P&L Format'[Account ID] IN { 2, 3, 4, 6, 7, 8 }

)

P&L Value measure: introduce additional subtotals and update SWITCH mapping.
The measure remains structurally the same, but it now includes extra subtotal steps to support the new middle levels (Products vs Services totals, Other expenses subtotal, etc.)

P&L Value =

VAR var_account_id = SELECTEDVALUE ( 'P&L Format'[Account ID] )

VAR var_value = SUM ( 'P&L Data'[Amount] )

VAR var_total_revenue_Products =‘Revenue Products’ now include “child” line items

CALCULATE (

SUM ( 'P&L Data'[Amount] ),

REMOVEFILTERS ( 'P&L Format' ),

'P&L Format'[Account ID] IN { 2, 3, 4 }

)

VAR var_total_revenue_Services =‘Revenue Services’ now include “child” line items

CALCULATE (

SUM ( 'P&L Data'[Amount] ),

REMOVEFILTERS ( 'P&L Format' ),

'P&L Format'[Account ID] IN { 6, 7, 8 }

)

VAR var_total_revenue = var_total_revenue_Products + var_total_revenue_Services

VAR var_total_other_expenses =‘Other expenses’ now include “child” line items

CALCULATE (

SUM ( 'P&L Data'[Amount] ),

REMOVEFILTERS ( 'P&L Format' ),

'P&L Format'[Account ID] IN { 13, 14, 15 }

)

#10 STEP III: More sub-levels

VAR var_total_expenses =
    var_total_other_expenses
        + CALCULATE (
            SUM ( 'P&L Data'[Amount] ),
            REMOVEFILTERS ( 'P&L Format' ),
            'P&L Format'[Account ID] IN { 10, 11 }
        )

VAR var_EBIT = var_total_revenue + var_total_expenses
VAR var_result =
    var_EBIT
        + CALCULATE (
            SUM ( 'P&L Data'[Amount] ),
            REMOVEFILTERS ( 'P&L Format' ),
            'P&L Format'[Account ID] = 19
        )

VAR var_ebit_of_total_revenue =
    FORMAT ( DIVIDE ( var_EBIT, var_total_revenue, 0 ), "0.0%" )

VAR var_amount =
    SWITCH (
        var_account_id,
        1, var_total_revenue_Products,
        5, var_total_revenue_Services,
        9, var_total_revenue,
        12, var_total_other_expenses,
        16, var_total_expenses,
        17, var_EBIT,
        18, var_ebit_of_total_revenue,
        20, var_result,
        var_value
    )

RETURN
    var_amount

Why this makes sense: each newly introduced “group” line (Products, Services, Other expenses) is rendered as a row in the statement, but its value is computed from the appropriate child Account IDs.

Plan Value measure: same pattern as P&L Value
No new logic is required. Only the same aggregation updates, applied to the Plan column, mirroring the Actuals behavior.

P&L measure: adjust which rows show “% of Total Revenue”
Because Total Revenue is now composed of more lines (and the revenue section spans more Account IDs), the threshold used to display the percentage must expand accordingly:

VAR var_account_with_percent =
    IF (
        var_account_id < 9,
        SELECTEDVALUE ( 'P&L Format'[Account] )
            & " <b> " & [% of Turnover] & " </b> of Total Revenue",
        SELECTEDVALUE ( 'P&L Format'[Account] )
    )

This aligns with the updated structure shown: all revenue-related rows fall under Account IDs 1–8, while Total Revenue itself is 9.

P&L Message and P&L Header measures
No changes are required. They remain valid because they are driven by the same base measures and are not dependent on the additional hierarchy.

Outcome
% of Turnover measure: update Total Revenue With this approach, adding new P&L lines or sub-levels is low-effort: you primarily update the Account ID groupings used in the total calculations. The statement layout (indentation, styling, and hierarchy rendering) remains controlled by the 'P&L Format' table and the existing HTML-based measures—so the report scales cleanly as the P&L structure evolves.
Finally, when the statement includes many rows, you can control whether the header stays fixed while scrolling. Use Format pane > Header formatting > Sticky header to turn this behavior on or off.

Conclusion
This guide showed how to build a pixel perfect P and L statement in Power BI using HTML VizCreator. The approach starts with a clean data model: one table for values and one for formatting, linked by Account ID. DAX handles all calculations and totals. HTML handles layout, spacing, and mixed formatting in a single column. You then refine the look by updating formatting metadata, not the visual. Deeper hierarchies come last by extending Account ID groupings, without changing the visual or core measures. This pattern exists because native visuals struggle with complex financial layouts, and Paginated Reports break interactivity. HTML VizCreator keeps everything inside Power BI while staying maintainable.

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

P&L STATEMENT

G U I D E

Custom Visual: HTLM VizCreator Cert

Introduction and Motivation

High Level Overview of the Solution

Step I – Data Model Design

Step I – Core Measures and Totals

Step I – Rendering Rows with HTML

Step I: Header and Dynamic Context

Step II: Improving Visual Aesthetics

Step II: Cleaner HTML and Styling Patterns

Step III: Adding More Sub Levels

Step III: Scaling the Pattern and Final Outcome

Why this use case?

#1 Introduction

#2 High Level Overview

Data model + starting measures

Aesthetics improvement

More sub-levels

#3 STEP I: Data model

#4 STEP I: Measures

#5 STEP I: Measures

#6 STEP I: Measures

#7 STEP II: Aesthetics

#8 STEP II: Aesthetics

#9 STEP III: More sub-levels

#10 STEP III: More sub-levels