P&L / Income Statement

Creating the perfect P&L income statement in Power BI with just one visual.

Power BI provides several methods to display a profit and loss statement, but often, they lack customization flexibility. We've tackled this challenge. With HTML VizCreator visuals, you now can create a fully customized P&L statement with just a few clicks.

As always, the following is just an example. Of course, you can create any dynamic table in Power BI, using our visuals. This gives you nearly endless options and will also speed up your report because the less visuals on a page, the faster the report 🤩

Below is an example of what we've achieved. Feel free to explore it and tailor it to suit your specific needs. This tool offers the perfect blend of functionality and personalization to enhance your Power BI experience.

Step 1:

In Power BI, we've set up two essential tables. The first table is dedicated to storing the data for your P&L statement, while the second table is designed to outline the structural rules for the P&L. At this step, you'll want to import your data table into Power BI and then go ahead to create your P&L Structure table according to your specific needs and preferences. 

To give you a clearer picture, here's how our two tables are structured: 

Table 1: P&L Data

Table 2: P&L Structure

In the P&L Structure table, we've defined the formatting details for each row. This includes specifying which rows are to be bold or underlined, the degree of indentation required for each, as well as the background and font colors. Additionally, we've added helpful hints that appear when you hover over each row, enhancing the user experience and providing quick insights.

Step 2:

Create the relationship between your two tables.

 

Step 3: Create measures

To make sure our P&L statement is dynamic, complete, and accurately compares actual values with planned ones, we need to set up a few specific measures. This step is key to ensuring that our P&L adapts to our data and covers all the essential elements we're looking to track and analyze.

• Measure: % of Turnover

To calculate the turnover percentage, first sum the 'Amount' from the 'P&L Data' table, storing this in `var_value`. Then, determine the total revenue by summing the 'Amount' for entries with 'Account ID' 1 or 2. Divide `var_value` by this total revenue to get the turnover percentage, and format the result as a percentage.

% of Turnover =

VAR var_value = SUM('P&L Data'[Amount])
VAR var_total_revenue =
CALCULATE(
SUM('P&L Data'[Amount]),
ALL('P&L Structure'[Account]),
'P&L Structure'[Account ID]= 1 || 'P&L Structure'[Account ID]= 2 )

VAR var_result =
DIVIDE(var_value, var_total_revenue , 0)

RETURN
FORMAT(var_result,"(0.0%)")

 

• Measure: P&L Value

To create the 'P&L Value' measure, begin by selecting the 'Account ID' and store it as var_account_id. Then, calculate var_value by summing the 'Amount' from the 'P&L Data' table. Compute var_total_revenue and var_total_expenses by summing 'Amounts' for specific 'Account IDs' (1, 2 for revenue; 4, 5, 6 for expenses). Combine these values to calculate var_EBIT. Next, determine var_result by adding a calculated sum for 'Account ID' 10 to var_EBIT. var_ebit_of_total_revenue is used to format var_EBIT as a percentage of var_total_revenue. Finally, employ the SWITCH function in var_amount to return values based on var_account_id, resulting in var_amount as the measure's output.

P&L Value =
VAR var_account_id = SELECTEDVALUE('P&L Structure'[Account ID])
VAR var_value = SUM('P&L Data'[Amount])
VAR var_total_revenue = CALCULATE(SUM('P&L Data'[Amount]),
ALL('P&L Structure'[Account]),'P&L Structure'[Account ID]= 1 || 'P&L Structure'[Account ID]= 2 )
VAR var_total_expenses = CALCULATE(SUM('P&L Data'[Amount]),
ALL('P&L Structure'[Account]),'P&L Structure'[Account ID]= 4 || 'P&L Structure'[Account ID]= 5 || 'P&L Structure'[Account ID]= 6 )
VAR var_EBIT = var_total_revenue + var_total_expenses
VAR var_result = var_EBIT + CALCULATE(SUM('P&L Data'[Amount]),
ALL('P&L Structure'[Account]),'P&L Structure'[Account ID]= 10)
VAR var_ebit_of_total_revenue =
FORMAT(DIVIDE(var_EBIT,var_total_revenue,0),"0.0%")

VAR var_amount =
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

To create the 'Plan Value' measure, follow the same steps as for 'P&L Value,' but this time, use data from the 'P&L Data'[Plan] column instead of 'P&L Data'[Amount].

Plan Value =
VAR var_account_id = SELECTEDVALUE('P&L Structure'[Account ID])
VAR var_value = SUM('P&L Data'[Plan])
VAR var_total_revenue = CALCULATE(SUM('P&L Data'[Plan]),
ALL('P&L Structure'[Account]),'P&L Structure'[Account ID]= 1 || 'P&L Structure'[Account ID]= 2 )
VAR var_total_expenses = CALCULATE(SUM('P&L Data'[Plan]),
ALL('P&L Structure'[Account]),'P&L Structure'[Account ID]= 4 || 'P&L Structure'[Account ID]= 5 || 'P&L Structure'[Account ID]= 6 )
VAR var_EBIT = var_total_revenue + var_total_expenses
VAR var_result = var_EBIT + CALCULATE(SUM('P&L Data'[Plan]),
ALL('P&L Structure'[Account]),'P&L Structure'[Account ID]= 10)
VAR var_ebit_of_total_revenue =
FORMAT(DIVIDE(var_EBIT,var_total_revenue,0),"0.0%")

VAR var_amount =
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: P&L

Once we have created the measures above, we can create our main P&L measure.

In the 'P&L' measure, var_account and var_account_id retrieve the selected 'Account' and 'Account ID' from 'P&L Structure'. var_account_with_percent adds a percentage of turnover to the account name for certain accounts (ID less than 3). var_account_hint pulls account hints. Styling variables like var_indent, var_background, var_font_color, var_bold, and var_underline set the appearance based on 'P&L Structure' selections. var_value and var_plan obtain 'P&L Value' and 'Plan Value'. The measure returns an HTML table, with each row displaying account details, P&L value, and plan value, styled according to the defined variables.

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

VAR var_indent = SELECTEDVALUE('P&L Structure'[Indent])*20
VAR var_background = SELECTEDVALUE('P&L Structure'[Background Color])
VAR var_font_color = SELECTEDVALUE('P&L Structure'[Font Color])
VAR var_bold = IF(SELECTEDVALUE('P&L Structure'[Bold])="x" , "<strong>","")
VAR var_underline = IF(SELECTEDVALUE('P&L Structure'[Underline])="x" , "<u>","")
VAR var_value = [P&L Value]
VAR var_plan = [Plan Value]
RETURN
//This Power BI DAX measure was created by BI Samurai.
"<table style='border-collapse: collapse; width: 100%;' border='1'>
<colgroup>
<col style='width: 60%;'>
<col style='width: 20%;'>
<col style='width: 20%;'>

</colgroup>
<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>"

• Measure: P&L Message

We add this measure to display a custom and dynamic message at the top of our P&L.

The 'P&L Message' measure begins by selecting the 'Region' from 'P&L Data' with var_region, defaulting to "all regions in total" if unspecified. It then calculates and formats the total 'Amount' from 'P&L Data' as var_value. The variance (var_variance) is determined by subtracting the sum of 'Plan' amount from var_value. Based on this variance, var_above_below is set to "above" or "below," and var_color to "green" or "red." The measure outputs an HTML paragraph stating the net result for the selected region, its value, and whether it's above or below the plan, colored accordingly.

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]),
ALL('P&L Structure')),"0,0")

VAR var_variance =
var_value -
CALCULATE(SUM('P&L Data'[Plan]),
ALL('P&L Structure'))

VAR var_above_below =
IF(var_variance >=0 , "above" , "below")

VAR var_color =
IF(var_variance >=0 , "green" , "red")

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>"

• Measure: P&L Header

Lastly, we introduce a header measure. Our HTML VizCreator visuals come with a handy feature that allows us to separately define and incorporate a header. This addition ensures that our Profit & Loss Statement has a well-structured and visually appealing header that complements the overall presentation.

The 'P&L Header' measure generates an HTML header for a Profit & Loss Statement with three columns: the first for the statement title and [P&L Message], the second and third for "Actuals" and "Plan," all styled and formatted for clarity.

P&L Header =

//This Power BI DAX measure was created by BI Samurai.
"<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: 19px;'>
<td><strong>Profit & Loss Statement<br></strong>in USD<br><span style ='font-size: 15px;'>"&[P&L Message]&"</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>"

 

Step 4:

Add one of the HTML VizCreator visuals to Power BI. If you don’t have the visuals yet, they are available on Microsoft AppSource.

Step 5:

From the measures and tables that we created, add the following fields to the visual.

Step 6:

The last step is simple: add a slicer that dynamically updates the P&L values. In our case, this slicer is based on the 'P&L Data[Region]' field, providing an interactive way to change between the two regions within the P&L statement.

And there you have it! You've successfully created a fully functional, dynamic, and entirely customized Profit & Loss statement. Feel free to explore and experiment with the various possibilities this setup offers. Enjoy the flexibility and control you now have over your P&L statement.