cancel
Showing results for 
Search instead for 
Did you mean: 

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

________________

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:

 

5.2 Fixed Format with Columns.png

 

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:

 

5.3 matrixes with buttons.png

 

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:

 

5.1 Create Bookmark Group.gif

 

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):

 

5.4 Bookmark pane with groups.png

 

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:

 

5.5 Bookmark pane with selected visuals only.png

 

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:

 

5.6 Header Table.png

 

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:

 

5.2 Create Fixed Format IS.gif

 

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:

 

5.7 Fixed Format IS.png

 

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:

 

5.9 selection pane.png

 

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:

 

5.3 Create Fixed Format Group and Bookmark.gif

 

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:

 

5.10 Matrix with columns.png

 

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:

 

5.11 matrix with columns and conditional formatting.png

 

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

 

5.12 Buttons.png

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:

 

5.13 bookmark groups.png

 

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.14 bookmark selections.png

 

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:

 

5.15  bookmark collapsed.png

 

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

 

5.4 Update Bookmarks for visible groups.gif

 

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:

 

5.16 Action for buttons.png

 

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:

 

5.17 completed IS.png