Income Statement Analysis: Part 1 of 5
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 parameter tables) and variances - Part 4: Adding KPI cards and Waterfall charts with dynamic axis/Arrow performance indicators and measures to identify good/bad performance for advanced conditional formatting - Part 5: Creation of a fixed format Income Statement (With running total measures) and creation of bookmarks to switch between the income statement formatsIncome Statement Analysis: Part 3 of 5: Format the page and add Dynamic Measure
In the previous posts (part 1 and part 2),, we set up a matrix visual which shows our total actuals, Prior Year Actuals and Variance to Prior Year for a given year/month (based on the slicer selection). In this section we want to take this a step further and choose to show either Monthly figures, or alternatively Quarter to Date or Year to Date for the selected year and month. We will also apply a background and theme to the report and will apply conditional formatting to the income statement matrix, dynamically calculating whether an increase in the number is good or bad (i.e. whether it is a revenue/profit or expense number). ________________ In this 5 part series, 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 parameter tables) and variances - Part 4: Adding KPI cards and Waterfall charts with dynamic axis/Arrow performance indicators and measures to identify good/bad performance for advanced conditional formatting - Part 5: Creation of a fixed format Income Statement (With running total measures) and creation of bookmarks to switch between the income statement formats ____________Income Statement Analysis: Part 2 of 5: Create Simple Matrix view with Actuals
In part 1 of this blog series, we connected to the folder containing the Monthly CSVs and the Reference Data Excel file and brought the data into Power BI. In this section we will create an initial set of calculations and show the results in a pivot-table style Matrix visual. ________________ In this 5 part series, 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 parameter tables) and variances - Part 4: Adding KPI cards and Waterfall charts with dynamic axis/Arrow performance indicators and measures to identify good/bad performance for advanced conditional formatting - Part 5: Creation of a fixed format Income Statement (With running total measures) and creation of bookmarks to switch between the income statement formats ____________Income Statement Analysis: Part 5 of 5: Add IS Tables and Bookmarks
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. ________________ 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 parameter tables) and variances - Part 4: Adding KPI cards and Waterfall charts with dynamic axis/Arrow performance indicators and measures to identify good/bad performance for advanced conditional formatting - Part 5: Creation of a fixed format Income Statement (With running total measures) and creation of bookmarks to switch between the income statement formats ______________________________
Gathering Stock Market Information Using APIs
If you want all your senior team looking at the same stock market information, creating a single report and pulling up-to-date information using APIs is a great way to achieve this. This blog gives you an end-to-end explanation of how to achieve this along with a Power BI Desktop file as an example.Column-Level Security - 2 Methods for Restricting Column-Level Information
Column-level security is not supported in Power BI, but there remain instances in which we are required to limit the columns each user can see. For instance, a manager should be able to see all their employees, their job title & other key information. However, certain information, such as their home address & national insurance number should be privy only to those within the Human Resources function. This article discusses 2 potential methods for achieving this.Dynamic Row Level Security (Rls) and Company Hierarchies
Having one version of the truth but limiting that version of the truth, depending on who logs in, creates personalised and consistent reporting. Finance and Accounting teams deal with high volumes of the types of sensitive information which require restrictions dependent on who logs in; this includes commissions, travel and expense reporting or dealing with the roll up of companies. This blog provides an overview of the problem and a step-by-step guide on building your first report.Income Statement Analysis: Part 4 of 5: Add KPI Cards and Waterfall
In the previous steps we built a pivot table-style Income Statement Matrix, created dynamic calculations for MTD/QTD/YTD and applied conditional formatting on the variance columns based on a measure. One of the key aspects of storytelling with data, however, is to make it easy for the reader to see performance at a glance and understand what is driving that performance. For this we will a set of ‘pimped up’ card visuals showing the key Income statement numbers, a comparison to the value last year and a visual, conditionally formatted arrow icon for each indicating whether the number has increased or decreased (and whether that increase is good or bad): To understand the drivers behind performance we will also build a waterfall chart that will show the Income Statement line value (defaulting to Profit After Tax but responding to when a row on the IS is selected) at the start of the month/quarter/year, the change in the value for each organisational unit and the corresponding value at the end of the month/quarter/year: To understand the drivers behind performance we will also build a waterfall chart that will show the Income Statement line value (defaulting to Profit After Tax but responding to when a row on the IS is selected) at the start of the month/quarter/year, the change in the value for each organisational unit and the corresponding value at the end of the month/quarter/year:
