Performance Financial Statements In Power BI ( Reloaded )
Financial Statements in Power BI Dashboards- V2!
Nearly two years ago we released our most successful blog post and corresponding video to date - Financial Statements in Power BI!
Since that time we have updated the way we build these in Microsoft Power BI and witnessed the joy of many CFOs. My colleague Chris has been nailed down a new methods that riffs off the original from two years ago. It eliminates the need for COMPLEX DAX and with speedy, cute measures that can be written in moments for even better data visualization.
Advantages include:
1) Front-loaded projects with increased sustainability
2) Simpler DAX, and…
3) Higher level of PBI precision.
Wait till Jeff see this.. He might actually let us get rid of SSRS.. JOKES.
Watch me, or follow along…
At the bottom of this page, you should find a video to view how it all comes together. Alternatively, you can follow along step by step in the blog below… enjoy!
The Business Analytics Data from Excel
Our data set has three sheets.
Income Statement (IS) Structure
Budgets
Actuals
IS Structure:
This data-set describes how the data is displayed.
ORDER helps keep.. well the order. Once this data-set hits PowerBI, It makes sure that the data is on the correct sort order.
ACCOUNTS is what maps an account to an order number. This is labour intensive the when you first build the report, but cuts down on complexity.
TYPENUM allows us to ‘collapse’ groups of rows. For example, we will be able to only show rows 9, 18, 19, 23, 24 from the range 8 - 25 when we choose level 2 later on.
FORMATTEDTITLE is just a visual interpretation of what the Order Column actually represents.
Actuals:
ACCOUNTKEY is just the account that the line represents
BRANCHKEY is not used in this example, but allows for Consolidations
DOCDATE is what day the Actual Transaction happened on
AMOUNT is the Actual
Budgets:
ACCOUNTKEY is just the account that the line represents
BRANCHKEY is not used in this example, but allows for Consolidations
CUSTOM is what day/month the Budget is mapped to
AMOUNT is the Budget
Loading the Dataset onto the Power BI Dashboard
The magic happens when we add in our relationships. By loading IS Structure AND a manipulated version of IS Structure ( IS Structure Mapping ), we can seamlessly join our actuals/budgets to our mapping file.
Load IS Structure
Load Actuals
Load Budgets
Finally load in the Mapping Data set. The key to full mappings is in the ‘Split by Delimiter’ and ‘Unpivot Other Columns’ functions.
Load IS Structure again
Only keep Order and Accounts, delete all other columns
Split by Delimiter, make sure to specify that you wish to split all instances of your chosen delimiter
Select Order and then Unpivot OTHER columns
Clean data as necessary
I would really recommend watching the video for this part, just from 5:45 to 8:20 ish if you are unsure of what you are doing.
After loading the data , you should map the data-sets like the image
Is Structure[Order] -> Is Structure Mappings[Order]
Is Structure Mappings[Account] -> Actuals[Account]
Is Structure Mappings[Account] -> Budgets[Account]
Or just see image…
The Dax
I like to have base measures and display measures.
Base measures contain Core Logic, Display measures take that core logic and expand on it in niche ways. We’re not saying is the BEST way, but this is a good starting point.
Examples might be:
Base: Ice Cream
Display1: Mint
Display2: Chocolate
or:
Base: Actuals
Display: MTD Actuals
Display: YTD Actuals
So this particular report has the following measures:
Compiling the Power BI Dashboard
Drop in a matrix and add the following to the rows:
Order from the Is structure
Formatted Title From Is structure
To the columns add:
Dates[Year]
Dates[Month]
To the values add:
SP Display
BUD SP Display
SP BUD Variance%
A Measure that = BLANK()
As for Formatting:
Remove all word wrap
Turn off stepped layout
Remove all Subtotals
On the drop down for Order, click include items with no data
In the end you should have something similar to the image above.
By spending time up-front and building comprehensive Income Statement and Balance Sheets you could potentially eliminate your entire financial reporting process.
Imagine a world where you no longer manually assemble your financials every month, cell by cell, page by page. Think of the business intelligence you'll get out of your data model. That all becomes irrelevant when you package your financial data in PowerBI. Slicers replace duplicated, time consuming tabs and your IS structure page ‘builds’ your report for you. That’s a pretty good deal.
We have seen this process completely change how some finance departments function. All around the world we have see this base method take a monthly, 3+ week long financial reconciliation and assembly process and turn it in to a ‘3 clicks and you’re looking at the latest data’ process. It’s turned some of our partners in to long-term fans of FreshBI.
The methods above are the simplest form of what can be done now. You can imagine that this report can be finessed to contain YTD numbers, off the wall KPIs and even prettier front-ends. That’s what we love about PowerBI.
Our Latest Blogs
About FreshBI
Operating throughout USA and Canada, FreshBI is a boutique Microsoft Partner using Power BI to unlock the value trapped in your data so that your business can succeed.