Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Find articles, guides, information and community news

Most Recent
ChrisMBarber
Advocate I
Advocate I

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.

Read more...

rsaprano
MVP

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.

 

________________

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

______________________________

Read more...

rsaprano
MVP

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

 

4.1 KPI cards.png

 

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:

 

4.2 Waterfall chart.png

 

 

 

Read more...

rsaprano
MVP

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

____________

Read more...

rsaprano
MVP

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

____________

Read more...

Helpful resources

Join Blog
Interested in blogging for the community? Let us know.