I mentioned in an earlier blog that I have been doing some work with the owner of the InfoRiver Power BI Custom visual. I have been testing, given UI feedback, and more recently I have produced some instructional videos. I wanted to share the first of these videos with you today because I think it tells a compelling story.
Build a P&L in 5 Mins
I have been building P&Ls in Power BI for about 6 years, and it’s pretty hard to do. When I was asked to build a video showing how this can be done in just 5 minutes, I was a bit sceptical at first. But as you will see in the video below, it definitely can be done.
There can be many challenges with reporting in Power BI. As long as all the aggregations and totals are doing the same thing, then it is pretty straight forward. I.e. if you want to add up all the transaction values, and create subtotals, then it is pretty easy to do. As soon as you want to add up some numbers, subtract other numbers, give some subtotals and some running totals, sometimes show positive numbers and sometimes show negatives, everything becomes orders of magnitude more difficult. What I like about the InfoRiver custom visual is that it has “spreadsheet-like” capabilities. Don’t get me wrong – it is not a spreadsheet, but it does have some in built capabilities that allow you to do somethings in a similar way you could do these things in Excel. The best way to show you is with a demo.
Video Demo
Two caveats first.
- The source data in my demo structured and clean. The biggest challenge for most companies I have worked with is that the Chart of Accounts is not well structured for the current operations of the business. If the COA is messy, then standard DAX is very complicated. Ironically, InfoRiver can actually solve some of the issues with a poorly structured COA, and I haven’t written any DAX to produce this P&L.
- The data I used in my demo is already loaded into a well structured star schema. It is not mandatory to do this, but everything is a lot easier if it is done this way.
The 5 min starts after these things are done.
You mentioned that you will have a separate video that goes through every single step needed to create this visual. Did you create that video and how do I find it?
You can find them by searching my blog for P&L
https://exceleratorbi.com.au/?s=P%26L
Excellent demo.
How are the headers and sub headers stored and created? This was cool? How do I check out this software?
The links to the software are in the post. If the header and sub header columns needed are not in the chart of accounts, they can be added as master data using lower query pretty easily.
There is a feature called Group in the toolbar and you can use that to create custom visual headers and sub headers easily as well . You can see it live here – https://youtu.be/gDS5oiszTvY
Looks impressive Matt!
Where does it store the data / metadata?
Hello Iman, Inforiver stores any changes to report and comments directly in the PBIX file itself as long as you have edit rights (in standard, professional, and premium editions as they are serverless editions). However, In the enterprise edition – we allow commenting in the read/view-only mode as we have the ability to write those comments into backend SQL storage as well as the ability to write back all visual level data as well as changes with timestamp and user information. The enterprise edition can be configured as a private/self-hosted on-premise version or as a SAAS edition as well.