Income Statement Analysis: Part 5 of 5: Add IS Tables and Bookmarks

[rsaprano]

________________

In this 5 part series, we walk through the steps required to build out the first page of the Income Statement Report here. 

The topics covered in these walkthroughs include:

- Part 1: Connecting to Data (Folder Containing CSVs), Importing into Power BI

- Part 2: Creating the base measures and an Income Statement Matrix visual

- Part 3: Adding advanced measures e.g. based on the period selection (covering use of disconnected pa...

- Part 4: Adding KPI cards and Waterfall charts with dynamic axis/Arrow performance indicators and mea...

- Part 5: Creation of a fixed format Income Statement (With running total measures) and creation of bo...

___________________

Congratulations! You’ve made it to the final step of completing the Income Statement page. We already have a pivot table style matrix with conditional formatting, Dynamic card visuals showing our key numbers (with a comparison to prior year) and a waterfall chart showing breakdowns for the month/quarter/year to date by Organisational unit.

In this section we will build different styles of the Income Statement. Rather than just having the expandable/collapsible headings, we will now have sub-totals such as Gross Margin and Operating Profit to appear in a more traditional fixed format style of reporting:

We will also build a third Income Statement which has Organisational Unit across the columns of the matrix:

As shown in the above screenshot, we will have buttons for the user to switch between the Income Statement formats, making use of the buttons functionality in Power BI.

1         Create buttons and bookmark group for Measure Comparison table

We start by adding another (temporary) report page as a holding page for new visuals to be incorporated as part of bookmarks

Cut and paste the Income Statement table from the current report page to the new page

On the new report page, create three buttons. The button text for them should be ‘Fixed Format’, ‘Measure Comparison’ and ‘Actuals By Country/Dept’

Assign a blue fill colour/white text to all the buttons except for the Measure comparison one which should have a black fill colour.

The report page should now look like:

In the selection pane (under view), select all the visuals on the page and then right click on one on the visuals on the canvas, select Group -> Group and rename this group to measure comparison:

On the Income Statement Report page, create groups for all the other visuals on there (E.g. groups for the KPI Cards, and one for the slicer/title):

Copy the group of visuals from the new page and paste them into our Income Statement report page

Create a bookmark on our Income Statement Page (with all visuals including the new ones visible) and call it ‘Income Statement-Measure Comparison’.  Select all the visuals groups on the selection pane and update the bookmark to not include data and to toggle for selected visuals only:

2         Create Measures for Fixed Format Income Statement

The labels for the fixed format income statement (e.g. Gross Margin, Operating Expenses) are all rows in our Headers table:

However, the sub-total rows do not show up when we simply use the Header field in our matrix because the GL Accounts are mapped to the main category headers rather than any of the subtotals (e.g. a GL Account will map to ‘Revenue’ but will not be also mapped to ‘Gross Margin’).

In order for the sub-totals to show correctly, therefore, we need to perform a different calculation depending on whether the label is a sub-total line or not. This is indicated by the CalcType column in the Header table: A calc type of 0 indicates that it is sub-total, whereas a calc type of 1 indicates that it is a main header category.

To actually calculate the sub-total values, we can make use of the order field in the DimHeaders table: each sub-total line is calculated as the sum of all the lines above it; i.e. all those header lines which have a value in the Order column less than the order

of the current sub-total line.

This is represented in the following measure which we need to create:

Running Total Actuals By Period = If(
     HASONEFILTER(DimHeaders[Header])
     ,
     CALCULATE(
          [Actuals By Period]
          ,
          ALL(DimHeaders)
          ,
          DimHeaders[Order] < VALUES(DimHeaders[Order])
          )
     ,
     blank()
)

This measure will only return a value when a specific row in the Header is in scope (which it will be for our matrix given that we are using the Header field in our rows) – this is the check for HASONEFILTER(DimHeaders[Header]), returning blank if this is not true.

If it is true, then we need to calculate the Total of the Actuals By Period Measure (i.e. the sum of all accounts) for where the Order is less than that of the current row.

What we then need to do is only use this measure for the sub-total rows (i.e. where the Calc Type is 0), for the others we can continue to use our Total Actuals By Period measure.

To select the Calc Type currently in scope we can just create a measure:

HeaderCalcType = min(DimHeaders[CalcType])

And then incorporating this into a measure which will show either the sub-total measure we  created above or the Total Actuals By Period measure:

Actuals Total or Running = SWITCH(
     [HeaderCalcType]
     ,
     BLANK(), BLANK()
     ,
     1, [Total Actuals By Period]
     ,
     2, [Running Total Actuals By Period]
)

We can then use this measure in our matrix with the Header field in Rows in our matrix.

We also need to create the same measures for Budget:

Running Total Budget By Period = If(
     HASONEFILTER(DimHeaders[Header])
     ,
     CALCULATE(
          [Budget]
          ,
          ALL(DimHeaders)
          ,
          DimHeaders[Order] < VALUES(DimHeaders[Order])
          )
     ,
     blank()
)

Budget Total or Running = SWITCH(
     [HeaderCalcType]
     ,
     BLANK(), BLANK()
     ,
     1, [Budget]
     ,
     2, [Running Total Budget By Period]
)

Variance to Budget Total or Running = [Actuals Total or Running] - [Budget Total or Running]

Variance to Budget Total or Running % = 
DIVIDE([Variance to Budget Total or Running],[Budget Total or Running],0)

3         Create the Fixed Format Income Statement

Ungroup the visuals on the temporary page and delete the Income statement table visual from this page.  

Create a new Matrix visual on the temporary page with DimHeaders[Header] on Rows, and ‘Actuals Total or Running’, ‘Budget Total or Running’, ‘Variance to Budget Total or Running’ and ‘Variance to Budget Total or Running %’ on values:

Apply the following formatting options to the matrix:

• Grid: Row Padding 13, Vertical/Horizontal Grid Off Outline Colour dark blue, outline weight 1px, text size 8px
• Column Headers: Text size 11pt, Word wrap on
• Row Headers: Text size 11pt , Stepped Layout On (Indentation as 10), Word Wrap On, +/- icons off
• Values: Font colour black, background colour white, alternate background colour light grey, banded row style on, Text size 11pt
• Subtotals: Row Subtotals On, Row subtotals label ‘Profit After Tax’, background colour blue/grey, per row level of

Ensure that the Header Line is sorted by Header (which is already sorted by the ‘Order’ column). Rename all the display names so that the matrix looks like:

Update the fill background of the buttons on the temporary page so that they reflect the Fixed Format button being highlighted (You can use the format painter from the other buttons):

Select all the visuals on the new temporary page and create a new group for them (e.g. Fixed Format Income Statement)

Back on the Income Statement page, hide the Measure Comparison Visuals group and copy and paste the new Fixed Format Income Statement Group as visible:

With all the visual groups selected (Measure comparison hidden, Fixed Format Income Statement group visible), create a new bookmark called ‘Income Statement – Fixed Format’. Update this visual with ‘selected visuals’ and with the ‘Data’ checkbox toggled off as with the previous bookmark:

4         Actuals by Country/Dept

The last version of the Income Statement we will be producing will be a simpler version of the fixed format showing actuals broken down by Region:

Delete the Fixed Format Income statement table on the temporary page

Create a new Matrix visual on the temporary page with:

• DimHeaders[Header] on Rows
• ‘Actuals Total or Running’ on values and
• DimOrganizations[Parent Organization]/DimDepartments[Department] fields on Columns.

Rename the display names – ‘Header’ to ‘FS Line’ and ‘Actuals Total or Running’ to ‘Actuals’:

Apply the following formatting options to the new matrix:

• Grid: Row Padding 13, Vertical/Horizontal Grid Off Outline Colour dark blue, outline weight 1px, text size 8px
• Column Headers: Text size 11pt, Word wrap on
• Row Headers: Text size 11pt , Stepped Layout On (Indentation as 10), Word Wrap On, +/- icons off
• Values: Font colour black, background colour white, alternate background colour light grey, banded row style on, Text size 11pt
• Subtotals: Row Subtotals On, Row subtotals label ‘Profit After Tax’, background colour dark grey, per row level subtotals off

The matrix should now look like:

Update the fill background of the buttons on the temporary page so that they reflect the ‘Actuals by Country/Dept’ button being highlighted:

Select all the visuals on the new temporary page and create a new group for them (e.g. Actuals By Country)

Back on the Income Statement page, hide the Measure Comparison Visuals group/Fixed Format Group and copy and paste the new Fixed Format Income Statement Group as visible:

With all the visual groups selected (Measure comparison/Fixed Format Income Statement groups hidden, Actuals By Country visible) , create a new bookmark called ‘Income Statement – Actuals By Country’. Update this visual with ‘selected visuals’ and with the ‘Data’ checkbox toggled off as with the previous bookmarks

5         Clean-up bookmarks and link buttons

Go through each of the three bookmarks on the income statement page and update the visible property of the visual groups on the selection group to only have the required income statement visible e.g. for Income Statement-Measure Comparison it should look like:

Select all of the visual groups and update the bookmark, ensuring that ‘Selected Visuals’ is selected:

Next, navigate to each bookmark in turn and for each button in the bookmark, link it to the appropriate bookmark by selecting Type ‘Bookmark’ under Action (Visualisations pane) and selecting the correct bookmark:

Ensure that the positioning of the buttons and the size/positioning of the income statement matrix is consistent between the bookmarks

Test the bookmark buttons to ensure they each show the right income statement.

Delete the temporary page.

_______________________

Congratulations! You have completed the Income statement page which should now look like:

Step 5 -Add Bookmarks.pbix