In this tutorial, I’m going to show you how to create unique tables in Power BI using financial report template designs. You may watch the full video of this tutorial at the bottom of this blog.
This will be a quick breakout session based on the financial reporting course in Enterprise DNA Online.
I want to show you a very detailed reporting application for financial metrics and data. Specifically, I want to show one of the techniques that you can utilize when creating template designs for financial reports.
This is a balance sheet which is set up in a unique way. You can see a summary and some granular details in the report. There are subtotals and roll-up calculations for each individual piece of information.
Now, I want to show you how to create a table that looks like this example, and how you can customize it depending on your requirements.
This will be a breakdown of the concept. If you want to master how to build financial reports, you should check out the full course in Enterprise DNA Online.
Building The Template In Excel
Now, I’m going to show you how I developed these Power BI report template designs.
The first thing I did was build the design in Microsoft Excel.
You can see that I have custom indentations inside the rows based on how I want the template to look. I wanted my subtotals to have more indentation so I pushed the space bar and indented it that far.
I also have a subtotal calculation which sits on its own at the bottom. I made sure that everything was set out in unique ways.
In the Power BI report, you’ll see that this calculates all those unique subtotals.
There’s no way of doing this directly inside Power BI without using this technique.
I’ve also created these common financial ratios at the bottom of the table in Microsoft Excel.
You can show unique calculations or whatever it is you want to show in your reports by utilizing this template technique.
This is perfect for financial reports because of their unique format. Although financial reports are usually static, with Power BI you can make these reports dynamic.
Creating The Template Design In Power BI
I’m now going to show you how I created this report in Power BI.
First, I brought the table I made in Excel into my model in Power BI.
I now have my Balance Sheet template. I’m going to then grab my Row Index to the page and turn it into a table.
It aggregates up because it’s a number.
Then, I select Don’t summarize and Show items with no data.
I have spaces in my report in Excel. So, I want to make sure that those spaces exist.
Selecting the two options will ensure that the spaces will show in my report in Power BI.
Now that I’ve got my Row Index, I’m going to then bring my template, which I called Balance Sheet Items, into my table.
I can now see my template design in my table in Power BI.
Now, in one of the recent releases in Power BI, Word wrap is turned on automatically inside the tables. So, if you’re not getting your template, go to Values located in the panes on the right-hand side, and turn the Word wrap option off.
Then, go to Column headers and also turn off the Word wrap option.
In the table, you can choose to leave the Row Index. But you can also choose to remove it by simply minimizing its column width.
You’ve now got this template design in Power BI. The next steps are just a matter of creating their calculations.
I’ve already made this lengthy calculation using the SWITCH TRUE statement for the report.
I just need to drag this measure into the report. This then allocates the results to the template.
***** Related Links *****
Power BI Reporting Templates Expanded – Power BI Visualization Concepts
Power BI Financial Reporting: Allocating Results To Templates At Every Single Row
Power BI Visualization Technique: Learn How To Create Background Design Plates
Conclusion
This technique is great because of its flexibility. You can create tables of any design and then easily allocate values to the table.
These template designs aren’t simply for financial reports. These could also be used for metrics that you want to put into a table.
All the best,
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resource
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events
