Comment on page
Profit and Loss Statement
How to create a profit and loss statement with Innofalls
Waterfall charts, also known as bridge charts, are often used to visually represent changes and variances in financial data. They show how various factors contribute to the overall change between two periods. Waterfall charts are particularly helpful when presenting financial statements that involve multiple line items or categories, allowing viewers to see the net effect of positive and negative changes. By displaying each component as a sequential bar with upward or downward movements, waterfall charts provide a clear understanding of the drivers behind the change being reported.
In this example we have a look at how to create a "profit and loss" statement with Innofalls.
You can download this example here. If you open it with Power BI Desktop, you need no license.
This is the chart we want to create:
Final Profit and Loss Example
This is a running totals waterfall chart with intermediate totals. Intermediate totals means, that expanded nodes of the first level are summing up all previous values and are shown as a sum from zero. In this case Total Revenues, Total costs and expenses and Net earnings.
In contrast, intermediate sub sums show expanded nodes as a summarizing change of all child nodes. See Earnings from continuing operations above as an example, which is on the second level.
The chart is created with default settings. No configuration has to be made.
Before we get into how the data for this example is structured, we examine the chart with collapsed nodes to understand how it is build up. When all nodes are collapsed, we get the following chart:
When everything is collapsed we have three nodes and a sum:
- Total Revenues (as the start sum)
- Total costs and expenses (as a change)
- Net earnings (as a change)
- Values (as the overall sum)
These values come from our top level dimension. All detailing data is hidden under these parent nodes and can be made visible by expanding these nodes. You can identify nodes that can be expanded by the little "plus" icon next to their category label.
Further you will notice that the two nodes in the middle are shown differently than in the example above - not as sums. To create a valid waterfall chart, the nodes in between are shown as changes and the result is added as a final sum.
Open the nodes by clicking the "plus" icon next. As soon as we open the three nodes, we get the following:
Now we see the second level dimension. Net earnings has become our final result and Values has been removed. If the last element in a running totals waterfall chart is expanded, it becomes the resulting value.
We are observing that our data is unbalanced. What this means is that some parent nodes have additional child nodes that can be expanded further, while other parent nodes only have the final or end nodes (also called leaf nodes) without any further subcategories.
Innofalls supports unbalanced hierarchies of arbitrary structure and depth.
When we finally expand all remaining closed nodes we get our full example from the beginning. All remaining nodes are part of our third level dimension.
The data behind this chart made up of three dimensions, each representing a level of our hierarchy:
- Top level
- Second level
- Third level
Our Values are in a single column.
This is the Excel sheet for our chart:
There are some important things to notice:
The values are only present for the final nodes (or leafs). The sums for the Top Level and Second Level nodes are calculated automatically.
If a row contains no value for Third Level, the value is assigned to the above level. This way we define different depths in the hierarchy.
To ensure the order of the nodes stays as we intended and prevent Power BI from resorting them in random order, we define a sort column for each level column and apply this order in the Data View in Power BI:
Sorting the third level
Now we import this data into Power BI and add the dimensions and values to an Innofalls chart in the following way.
After adding this data and expanding all closed nodes we are done. We don't have to configure anything. You might consider choosing the IBCS-Colors theme if you prefer the following look: