In this particular tutorial, I’ll be going over some integrated Power BI Financial Reporting techniques which was covered in an Enterprise DNA Learning Summit in the past. You may watch the full video of this tutorial at the bottom of this blog.
Integrated Reporting provides greater context for performance data and decision-making. When completing a financial reporting analysis in Power BI, it is highly likely that you will need to integrate data from various tables into a format or data structure that works well within the Power BI data model setup.
Getting your data model right is essential. To get your data model right for financial reporting in Power BI you need to do a lot of hard work within the query editor.
I’ll show you some raw data and how to manipulate it inside the query editor. This is to get optimized tables before placing them into a data model. Ultimately, you must have these tables set up correctly so that the right relationships can be made between them.
You can integrate elements of your data and calculations then drag them into a table feature of Power BI. This can certainly create some really compelling financial analysis.
Now, I’ll review the usual financial integration technique. This is for you to be able to see the main issues that we need to solve here. I was actually able to solve it by visualizing the output and then configuring my model and tables in a way that I could integrate my revenues and expenses.
Analyzing The Financial Table
So, what are the issues? Let’s take a look at this example.
Traditionally, the table features in Power BI were not that good but they’ve been improved a lot. I don’t want to go too far and explain how to set that up in detail, because I’m going to do that in the learning summit.
We’re going to have a session for one hour regarding financials, and it’s completely free. All you have to do is to register here, and then join the live virtual event.
As you can see from our sample table below, the Revenues were separated from your Expenses. Both were generated in a slightly different way. Your Expenses could be in some different program that monitors everything that’s going on within the business or all the invoices, for example. But, your Revenues will usually come down and be derived from some sort of transaction table.
I’ll be showing you that it doesn’t need to be that complex to be able to do and create this integrated financial reporting.
Using SUMX For Integrated Power BI Financial Reporting
Now, take a look at this table. This is basically every single transaction that this company has made for a number of years, and we don’t even have a Revenue column here.
So, inside an iterating function (SUMX), I multiplied the Order Quantity by the total Unit Price to get my Revenue. I named this measure as Total Sales, which is within my Key Measures group.
Now that’s our Revenue table. However, there is still a big issue that’s underlying here. And that’s the next thing that I’m going to point out.
Analyzing The Expenses Table
Let’s check out our Expenses table here.
Now, look at the data that came out for our expenses in this Expenses table. You may have something very similar to this, or you may not. This example is only based on how I’ve seen expenses laid out.
Judging by the way this table looks right now, your revenues and expenses definitely can’t be integrated into one table. There is absolutely no way in Power BI that you can integrate your revenues and expenses in just one table.
But that’s what I’m going to emphasize in this tutorial. I’ll be showing you that we can integrate these two elements of our financials together and get them into one table. We just need to do a few different steps. We will explain this in detail during the learning summit, so I’ll just go over the steps quickly.
Using Query Editor For Integrated Power BI Financial Reporting
This Expenses table here was not set up well for Power BI. So, the first thing that we need to do is to put all these elements in one column.
To do that, we need to bring in the table through the Query Editor. Just click the Edit Queries option under the Home Tab.
Then, click the Expenses query under the Data Model.
And this is how it looked after bringing it to the table. You’ll see here that we already have the dates across the top.
If I scroll down to the bottom of the applied steps, this is how the Expenses table looks. Here, you can see that we already have the Expense Item, Expense Category, Expense Month, and Expense Value.
I was able to do that by Unpivoting. But I won’t cover it here, but I will certainly show you how to do that to all those who register for the learning summit event later on.
Using SUMMARIZE For Integrated Power BI Financial Reporting
However, we still have some issues to look into. Let’s try to check out the Sales Table.
As you noticed, there’s still no aggregation in terms of the way we want to aggregate revenues. We need to somehow get a table in a similar structure to the Expense table. Then, combine them together so we can create a more organized table for our financials.
Unlike the Expenses table, I won’t do this in the Query Editor. I’ll be doing it inside the tables instead.
The first thing that I want to do is to look at my revenues. I wanted my revenues to be broken down by brand and month. This is because my expenses were by month as well. I want to try and formulate something similar to that.
In that case, I used the SUMMARIZE function to create a table function of my Brands and my Month & Year columns.
I’ve also included a category of revenue because I’m going to integrate this into my Expenses table.
After that, I created the First Date which will enable me to actually get a date, and will serve as the key when we try and link this up to the date table at the end of the day.
Lastly, I’ve got my Sales Values. I’ve used my Brand Revenues measure and branched it out by incorporating it into the SUMMARIZE function. And now, I have my revenue broken down by month and brand.
Now, let’s check it out in the Report View. As you can see, the Revenue now has three different line items per brand. This was the result of the Brand Revenues measure that we’ve already reviewed.
Using The UNION Function
Furthermore, we’re going to go deep into the actual integration where I used the UNION DAX function.
UNION is a table function that appends one table on top of another. By using the UNION function, I’ve appended Expenses onto another table (Brand Revenues) of the table that I’ve just created. I did that because I needed to match these columns up exactly for the UNION function to work.
Then, I used the SUMMARIZE function to set up the table with the exact columns that I wanted in the exact order. This is to match them up with the exact Expenses table that I generated in the query editor.
Now, we have this one table that shows the Expenses. And when you scroll down to the bottom, you’ll see the Revenues. They even appear with the brand name due to the Brand Revenues part of the table function as well.
I’ve also included an Index column because I want to make sure that I can sort the table correctly.
Now, we have integrated two completely separate tables that weren’t totally optimized to create any sort of financial reporting. We’ve brought them all in automatically. There’s no manual intervention that would be required in Power BI.
Creating A Relationship Within The Data Model
Earlier, I had to bring in a date which is the Months column.
Because of this particular column, I can create a very simple relationship from my Date table in the model.
This is essential because we’re going to do some time intelligence or time comparisons type of analysis with all the great time intelligence functions. We will then create a simple relationship that flows down to our financial details as well.
Consequently, we can create our table from the Financial Details table. We don’t even have to worry about the rest of the other core models, which are going to be used for all the other analysis that we’ll be going through during the learning summit.
Now that we’ve created this table, we can then start running all our analysis on our financials. As you can see here, I’ve got a group of Financial Analysis measures.
And I’ve also got all my insights in this table. We were able to create a really simple table that enables us to see our Revenues, Expenses, Cost of goods sold, and so on.
Summary Metrics And Financial Breakdown
You can also see a summary metrics below in the table.
One of the summary metrics allows us to check the comparison between our Revenues and our Cost of goods sold.
There’s plenty of interesting things that you can do when you create or optimize your tables. In this case, we have integrated data from different tables within our financial reports.
And it has enabled us to drill into any aspect of our financials because it is all in one table.
Let’s take this one for example. We can easily drill into our Expenses and Cost of goods sold and then quickly review our Actuals, our actuals last year (Actuals LY),and our difference last year (Diff. vs. LY).
And that’s how you can do integrated financial reporting in Power BI. You can do it for our financials and accountancy type of work in Power BI. And you don’t need to regenerate tables like in Microsoft Excel.
In terms of the data sets that you already have, you can find a way inside Power BI to integrate these things out of your live data. It is just about finding ways for you to create the integration using those steps that I have discussed. Then, you can integrate it into your model in a really intuitive way.
***** Related Links*****
Expanding On Financial Reporting Templates, Next Enterprise DNA Member-Only Event
New Financial Reporting w/Power BI Official Course Launch
Customer Analysis In Power BI; Reviewing Performance Over Time
Conclusion
I hope I was able to give you ideas and possibilities with this tutorial. I also hope you’ve seen what you need to get right behind your report pages to highlight your analysis and visualizations effectively.
You can definitely complete some incredible financial reporting inside Power BI. You can create dynamic visualizations and tables which will highlight all your key financial metrics. It will also enable you to analyze backward-looking or forward-looking data in a seamless way.
So, as I’ve said, we are going into further details during the learning summit. If you’ve registered for the learning summit, you’ll get a copy of this resource free. For those who’d like to register and join the event, just click here. I’m certainly looking forward to seeing you there.
For more advanced data transformation and modelling examples that you can use within Power BI, please check out this module at the Enterprise DNA Online – Advanced Data Transformation & Modelling
Sam
