Written By Michael Boisvert
Custom Financial Statements do not come naturally to Power BI. There’s no way to denote sub-totals, no way to make easy, ordered groupings and no way to set the layout. The options available are generally formatting options applied to entire columns or values based on their comparators ( See: Conditional Formatting ) - never a specific set or subset of Rows. Basic functionality for Accountants is lacking. Judging by the customization options available to use in Matrices and Tables, Paginated reporting in Power BI was never a priority but this challenge has been met with strong integration with Power BI and Excel though features like Analyze In Excel.
This means Accountants are not comfortable in PowerBI. So How do we bring them back?
Hackers gonna’ Hack
FreshBI has turned the PowerBI Performance Financials process into something we’re proud of, given the limitations in paginated reporting in Power BI. What used to be out of reach for every accountant is now a reality; custom paginated reports in PowerBI are functional, conceptually easy and fast (depending how deep you venture down the DAX hole)
In a nut-shell, we design a structure that a ‘Display Measure’ can use to decide which formula to should be displayed in the values section of the matrix. Our design structure is a static table ( Excel / ‘In-Memory’ / SQL server ) with at least 3 columns: Index, Grouping, and Formatted Header. Index maintains the order of the structure, Grouping is consumed by the Dax to gather the correct account(s) (Take note that this does NOT have to be one data type. Some Lines could be single accounts, some lines could be whole departments. We’ll get to this later.) and Formatted Header is what we actually see when we’re viewing the report.
Our Dax Formulas start with the ‘Display measure’ which ‘switches’ the output formula. In the ‘Display measure’, we check which formula we should be using and retrieves it to the matrix. Inside a ‘Display measure’, we would probably have 3 ‘Base measures’: Single Account, Low Level Grouping and High-Level Groupings. The Dax is complex, but I’ll do my best to explain and provide examples.
Here are few points highlighting the utility and features of Statements in Power BI
Tailored Statement Layout
Bolds, Italics and underlines are still missing in PBI tables (you can vote for it here), however we can include pipe characters and white space to achieve an indended effect.
Custom Account Grouping
While Groupings are feasible in PowerBI, they require a hierarchal data set. Hierarchal data sets are not inclined to be sorted, or include nice formatting. By using the Structure method, we can just hard code our Grouping sets.
Row-Level Control
We get to decide the rules. Every row can have an ID or Grouping type (L1, L2, L3.. etc). The possibilities are endless.
Time Intelligence
Need MTD? Check. Need QTD? Check. YTD vs PYTD? Easy. This structure does nothing to limit the standard Dax Formulas we’re used to. Not to mention if you set it up properly, it’s contextual as well.
Drill-Through
We can leverage the index keys or grouping keys to Drill Through pages. This means from a P&L we can dive straight in to the GL transactions make up the Drill Through value. We are not covering this in this episode.
Expand & Collapse Layouts
The Grouping keys play a big role in collapsing sub sets of data. We can exclude all single account lines, all Level 3 groupings- whatever the board room calls for, this format can handle it.
So how do we get the Accountants back?
Well we can't get them all back- but a good start is by implementing custom tables in PowerBI.
Here are some steps we overcome in this guided tutorial.
How do I define my layouts ?
How do I define my structure and additional Dax info ?
What Dax measures do we need? ?
How should I structure my Matrices ?
How can I collapse the dataset ?
All of these are answered in the steps below, or the video at the bottom!
I encourage you to follow along! Its quick, easy, and a major step in creating a rich analytical environment.
Step-by-Step Instructions
We need to define what the Financial statement should actually look like. In this example, you can see I’ve added Groupings, SubTotals, Single accounts and spacings.
Here you can see three columns have been added: Index, Group Type and Group Set. Index keeps the order when the Header is displayed. Group type defines the level of grouping we’re performing ( if any ) and which Dax Measure to call. Level 0 Indicates a blank row, Level 1 indicates a title row, Level 2 indicates a level two sub total, etc.. Group Set tells the Dax formula which accounts to group. Level 6 Groups are just single accounts wheras Level 2 Groups are all accounts that start with two specific numbers.
Now that you have a format, it needs to be brought in to PowerBI. I'm importing it as 'Assets Display Table' because this dataset is only for Assets.
We need 6 Dax Measures to make it work. The Display measure switches which measure gets called based on what row is calculating. You can find those measures here..
Drop in index and formatted title in to the Row headers and your Calendar Elements to your columns. Set Index to show items without data. Turn off Word Wrap in the Column Headers and Row headers. Turn off Stepped layout and totals as well. It will throw an error till you drop in the display measure. Make sure there are NO relationships between the data and the display table.
Throw in the display measure and format to taste. At this point it should be fully functional.
7) Bonus Step: Collapsable Statements:
We know that sub totals are independent from each other and single account rows. That means we can exclude or include any set of our group types. Above we’ve Excluded single account lines.
In addition to the points covered earlier on in this blog, there are a few more benefits that I feel should be mentioned.
Cross Business Reports: This is the way forward if you want to build 'perfect' looking reports for every level of your business.
Semi-Dynamic Financials: This method mentioned above mean the totals will never lie!
Accountants in PowerBI: Finally you can tell the accountants there's no excuse! PowerBI has everything they need now!
Thanks for reading and I look forward to posting with you again.
Our Latest Blogs
Our Latest Blogs
Michel Burnett
Cash Flow Sensitivity and Scenarios
Michel Burnett
Michel Burnett
Craig Juta
It’s OK to Micro-Manage Your Cash Flow
Craig Juta
Craig Juta
Madeline Henderson
The FreshBI Edge: Powering Tomorrow's Business Intelligence Solutions
Madeline Henderson
Madeline Henderson
Colin Frank
The Objective Vein: Visualizing the Current of Cash Flow
Colin Frank
Colin Frank
About FreshBI
Based in USA and Canada, we have helped hundreds of businesses achieve excellence & success through business intelligence.
Power BI runs in our blood, and we are ready to take your business to next level.
Contact Us
Power BI
Michael Boisvert
As an expert in Power BI financial reporting and custom financial statements, I've worked extensively with the challenges posed by the platform's limitations and have successfully implemented solutions to address them. I understand the difficulties accountants face when working with Power BI, especially in terms of customizing financial statements, subtotals, and ordered groupings. My knowledge extends beyond theoretical concepts, as I have hands-on experience in overcoming these challenges.
The article by Michael Boisvert discusses the limitations of Power BI in creating custom financial statements and introduces a solution provided by FreshBI to enhance the performance of Power BI financial reporting. Here's a breakdown of the key concepts and features mentioned in the article:
-
Challenges in Power BI for Accountants:
- No way to denote sub-totals.
- No easy way to create ordered groupings.
- Limited layout customization options for financial statements.
- Lack of basic functionality for accountants.
-
Integration with Excel:
- The article mentions strong integration with Power BI and Excel through features like "Analyze In Excel" to make it more user-friendly for accountants.
-
Custom Paginated Reports:
- FreshBI has addressed the challenge by turning the Power BI Performance Financials process into a reality for accountants.
- Custom paginated reports in Power BI are functional, conceptually easy, and fast.
-
Design Structure for Display Measures:
- A static table (Excel/'In-Memory'/SQL Server) with at least three columns: Index, Grouping, and Formatted Header.
- Index maintains the order, Grouping is used by DAX to gather the correct accounts, and Formatted Header is what users see in the report.
- DAX formulas start with the 'Display measure' that switches the output formula based on the structure.
-
Utility and Features of Statements in Power BI:
- Tailored statement layout using pipe characters and white space.
- Custom account grouping with hard-coded grouping sets.
- Row-level control with the ability to define rules for each row.
- Time intelligence for MTD, QTD, YTD vs PYTD, and contextual DAX formulas.
- Drill-through capabilities using index keys or grouping keys.
- Expand and collapse layouts based on grouping keys.
-
Steps to Implement Custom Tables in Power BI:
- Define the financial statement layout with groupings, subtotals, single accounts, and spacings.
- Import the format into Power BI as a specific dataset (e.g., 'Assets Display Table').
- Use six DAX measures, including the 'Display measure' to switch between measures based on row calculations.
- Configure the Row and Column headers, turn off Word Wrap, Stepped layout, and totals.
- No relationships should exist between the data and the display table.
-
Bonus Step: Collapsable Statements:
- Subtotals are independent, allowing exclusion or inclusion of any set of group types.
- Provides additional benefits like cross-business reports and semi-dynamic financials.
-
Additional Benefits:
- Cross Business Reports: Enables building perfect-looking reports for every level of the business.
- Semi-Dynamic Financials: Ensures accurate totals.
- Empowers accountants in Power BI with all the necessary tools.
The article concludes with a call to action for accountants to implement custom tables in Power BI and highlights additional benefits and recent blogs from FreshBI.
For more in-depth guidance, the article suggests referring to the step-by-step instructions provided in the blog post or video.
