If you want to create a financial statement with drill-down functionality, see an example of a drill through in Power BI in this video. The video transcription is provided below.
Related Resources
- Data Visualization | Microsoft Power BI
- How to Create Power BI Dashboards
- Watch on-demand: Solver Power BI Series Episode 1: Getting Data
Do you have questions about using Power BI? Connect with us to learn more.
The Insights & Automation team at Forvis Mazars can help organizations use technologies, like Power BI, to gain efficiencies. Service offerings include business intelligence and Microsoft Power Platform assessments, along with software implementations and app-to-app integrations. We are a Sage Partner, Microsoft Dynamics Gold Partner, and 2022–23 recipient of the Microsoft Inner Circle award, in addition to the Solver Evangelist of the Year award.
Transcript
0:01–1:00
Hello, my name is Charles Allen. I’m with Forvis Mazars’ Insights & Automation group. In this video, we’re going to look at one way to get some income statement type information and a visual and then enable the user to drill through to get more details. A lot of companies want to use Power BI to display financial report information.
As with anything, there are multiple ways to achieve that one goal. But in this video, we’re going to show you one way and then in another video, we'll show you another way. So, let’s take a look at one way to display income statement information and enable the user to drill through to details.
1:00–2:04
Here we are in Power BI, as you can see, and we’ve got various elements on this page. The one key one is in the middle and that’s showing you income statement data by quarter. Now this can be expanded and it can be taken backwards if we go back up and hit the arrows.
Now, we’re looking at monthly. What I want to do is drill down to the details and run a drill-through report. One thing about Power BI is that drill-through capability is not automatic. In some systems such as the Solver Cloud financial reporting solution, there’s the automatic ability to drill down from a report to get details.
2:04–3:40
In Power BI, a developer has to add the detail to a page and then we drill through the report to that additional page. Let's see how that works. First of all, let’s look at how this report showing our numbers was created. You’ll notice we have five items:
- Actual Revenue
- Actual Cost of Sales
- Actual Gross Profit
- Actual Expenses
- Actual Income
Each item was created using an expression. The expression was created using a language called DAX, data analysis expressions.
If you look over here on the right, under the Measures Group, we have these items that are checked. If I want to see how I created the Actual Revenue, I’m going to click on it. Then, up here is our expression. We’re calculating the sum of the monthly amount and we’re multiplying it by -1. What that means is our Actual Revenue is typically a credit balance. We want to flip it around and make it a positive number. Then we are filtering our data for only the accounts that are revenue accounts. In addition, we’re filtering our data so we only pick up what’s called “actual data” as opposed to budget or something else. We also have other expressions for different line items.
3:40–5:08
If we look at Actual Gross Profit, all we’re doing is taking the revenue minus cost of sales. If we look at Actual Income, we’re taking gross profit minus expenses. So not hard to do at all. If you understand what your data is, you can do this. Now, here’s where the drill-through comes in. As I said, you have to build a page so it’s all ready for you to do this.
If I go to my revenue detail page, you’ll notice this item here, Actual Revenue. Actual revenue, if you remember, is a Measure. We have this Measure already as a filter. But notice the ones that are italicized. They are not defined by me—they are created automatically when I drill through the first page to get to this page. It’s inheriting those three items—month, quarter, and year—from that prior report. If I were to drill from another period, you would see those items change.
Now, as I said, we’re doing this one way. You notice I have another page called Cost of Sales Detail.
5:08–5:34
Let’s say you had 10 different line items on that first page. So maybe you have a lot of different expenses or different revenues and things like that. You could build a page for each one of those with different details. Maybe it makes sense to show some details on one page, but not another. That’s an advantage of doing it the way I’ve done it here, by using specific Measures to get the information.
5:34–6:32
Let’s take a look at how this works.
If I go over here to “Qtr 1” again to my Actual Revenue and I right click on it, notice that I have an option called “drill through.” With it, I can drill through to the Revenue Detail—that’s a page at the bottom.
I’m going to click on Revenue Detail. Now, notice down here are the automatic filters. We have the month, quarter, and year. Notice that quarter is showing “Qtr 1.” The data to the left also is showing “Qtr 1.”
If I go back to page one and go to “Qtr 2,” same thing … Now the filters section shows “Qtr 2.” The system is automatically creating those filters to get those additional pieces of information to only show what you want to see.
6:32–7:28
Now, I’m not showing transaction level detail, but if I had it, I could. As I said earlier, you might have different types of information you want to show depending upon the source. For example, you might have revenue data that’s not in the general ledger (GL)—it’s from your sales system. So, imagine having a drill through not to the GL transaction detail, but to your sales system detail. That’s something you can do with these drill-through reports.
You can create multiple drill-through reports and each report page can have different information. In addition, each page doesn’t have to have just one matrix. It can show different charts as well. Really, the sky is the limit. You can do whatever you feel like as long as you can build it and it makes sense.
7:28–9:00
To review, if I go back to page one, and if I want to look at “Qtr 3” Actual Cost of Sales, I can right click, drill through on Cost of Sales Details, and there it is: Cost of Sales, “Qtr 3,” Total 6,937,469.51.
There you go—that’s drill through!
In this video, we’ve created a financial report (a small one), created a drill-through (fairly straightforward). Now, users can use this information and Power BI to do it.
In our next video, we’re going to show you a completely different method of creating this financial detail.
Until we meet again, thank you very much for watching.