Welcome to part 2 of my series of easy Profit & Loss and other account statements in Power BI and Excel. In the first part I introduced the general principle of creating asymmetric shaped reports who use just one measure per column (you should have read this article in order to understand this post here).
How the technique works
This technique capitalises the aggregation power of the Vertipaq engine and creates a bridge-table between your DimAccount-table and the ReportsAccountsLayout-table. In there for every line of your report, all accounts that belong to the (sub-)totals are matched (“AccountsAllocation”). This table can get very long, but the engine can handle this easily:
Different use case: Account-groups-tables
In the first example we’ve worked with a chart of accounts, which had a parent-chield-hierarchy defining all the subtotals of the report. In this example we’re working with a different setup, using the good old DimAccountsGroups-table. Just one row per account and the columns are coming in pairs, containing the group-criteria and the sort-order for the report:
Individual Report-Layout
We also need a second table (ReportsAccountsLayout) that holds the definitions of the report-layouts like this:
As you can see in column Group and Key, we’re matching items from different grouping-pairs here, in order to design a very individual report. So far this could only be created in Excel using cubefunctions and/or in Power BI using a large switch-measure choosing between many different measures.
Using this technique, we only need one measure per column. And thanks to FrankT, who pointed out in the comments to my first blogpost that we can utilize bidirectional filters in PowerBI, the simplest measures can be used:
MySimpleMeasure := SUM(MyAmountColumn)
But in PowerPivot in Excel we don’t have bidirectional filters yet. So we still need the magic measure, that wraps a CALCULATE around the existing measure with the name of our bridge-table as a filter like this:
MyMagicMeasure := CALCULATE([MySimpleMeasure], AccountsAllocation)
Dynamically create the Bridge-Table
In order to dynamically create the bridge-table (“AccountsAllocation”) between the DimAccounts and the Report-table, we need some M-agic again. But this time it’s much simpler. First we need to create some keys in the ReportsLayoutTable:
- AccountScheduleKey: Will connect to the bridge-table and contains the name of the report (AccountSchedule) combined with the row number from the report where each account belongs to
- AccountGroupKey: Will connect to the DimAccountGroup in order to retrieve the account-group-allocation that is needed to allocate the single accounts to the AccountScheduleKey
ReportsAccountsLayout-Transformation (click on the picture to enlarge view)
The table AccountAllocation will be created using the following code, which transforms the wide short DimAccountGroups-table into a narrow long one:
- unpivot on the groups in order to create one row per account-group-allocation and
- subsequent merges of columns to create the keys, followed by a
- merge with the ReportsAccountsLayout-table in order to retrieve the AccountScheduleKey:
AccountsAllocation (click on the picture to enlarge view)
Things to consider
Nice side-effect: If you already have an existing model using the “good-old-accounts-group-table”: You just have to add this new logic and can still continue to use your “good-old-reports” or do further quick-analysis with it.
Hint: If the sort-order-columns of your current model cannot easily be identified by their name like in this example (prefixed by “Key*”): Create a translation-table for your column headers in order to filter on the relevant columns.
Watch out: There are filters applied in the file according to this technique: So don’t forget to turn them off after you’ve connected to your data and want to see all your figures!
Also check out the follow-up on this post with techniques who don’t require specifying the individual accounts any more!
As this solution has a certain business-value, you have to “pay” for the download by subscribing to my blog (also you have to be logged in, in order to download):
AccountSchemePart2.zipExcelAccountSchedModelPart2Excel.zip
Enjoy & stay queryious 🙂
Good Post, But still doesn’t solve the need I have. None of these report layouts work with a My chart of accounts out of Great Plains.
I am still wondering what purpose this even serves as you would need a simple 10 – 50 row chart of accounts which would only work for very small companies.
This is also unmanageable by regular accounting staff. Taking a 3 thousand plus chart of accounts and trying to monkey around with it like this is just not reasonable. Performance would be an issue and if even 1 simple change in the account structure was needed you are stuck re-creating every report.
There may be steps or information missing here, but I just don’t see how this would work. Way too complicated and prone to performance and Manageability issues.
Check out the new comfort-functions for this approach: http://wp.me/p6lgsG-qC
Larry i understand your frustration having worked with large COA (chart of accounts) but once the COA is mapped then these techniques do work with minimal maintenance. Of course it needs the ‘data steward’ to maintain these mapping rules but in a large organisation this is managaeble with simple business rules when new accounts are created then the necessary good practice ensures the new accounts are alerted to the data steward for maintenance.
If you could commit 5 minutes to explaining your COA and requirements then i am sure someone here will help you.
If you are a large organisation then you probably have many ‘accountants’ faffing around with their own view of the data where if they invested time in this model then you would have a solid datamodel with a single view of the truth for your Power BI financial reporting needs.
GO on challenge us and Share your requirements.
Hi David,
thx for the positive feedback!
There will be a follow-up on this post covering techniques that don’t require the maintenance of single accounts.
It’s always a matter of what you need and how you prefer to work and I have the feeling that this series will have a lot of chapters 😉
So stay tuned & queryious 🙂
Hi David, the comfort-functions are out now 🙂 http://wp.me/p6lgsG-qC
Pingback: Comfort Functions for Easy Profit & Loss statements in Power BI and Excel - Part2 – The BIccountant
Hi Imke. I have used your article for developing my new Power BI dashboard. Thanks very much for it. It has been very useful. I made a Qlikview dashboard in a similar way (I come from Qlik world).
I want to add a new column to the table with the % of the turnover value for each row. It is becoming a nightmare, because I have values just in the rows which use it for their calculations. Any easy idea? I have a measure like this:
Turnovers = SUMX(CALCULATETABLE(Fact_table;Account_ID=Turnover_value);Amount)
where Turnover_value is the value of the group of accounts of sales.
Then, I divide de value of each row by this measure, but I just have value for the rows that have the Turnover_value in their formula. The other ones leave blank.
Hi bimtng,
thank you very much for this nice feedback.
This measure gives me the share of turnover per row in my model:
Turnover% = ABS(DIVIDE([Act],CALCULATE(SUM(FactActuals[Amount]), FILTER(ALL(ReportsAccountsLayout), ReportsAccountsLayout[AccountName]=”Income” ))))
You need the FILTER(ALL -component here in order to go beyond the current filter context and get the turnover-value for every row in the report.
PERFECT ! It works wonderfully. Thanks very much.
Pleasure – you’re welcome.
I would be very interested in some stats:
1) How many different accounts do you have?
2) How many entries in your fact table?
3) Are you happy with the performance of this approach?
Thx a lot!
Of course I answer your questions:
1. I have about 23.000 accounts.
2. In my fact table about 3,5 million facts (just last 3 years).
3. About the performance, the dashboard is pretty new already. Today I have finished the first release and it is not tested enough yet. I will give you feedback in a few days. Right now it works fine.
I am going to improve the dashboard with the balance sheet (same way of doing things, but with different accounts). I will tell you.
Thank you.
thx – very much appreciated!
Hi Imked.
I have improved the dashboard with the balance sheet, and it works fine at the moment. The performance is great. No delays at all. As a clue, I use subrogated keys to link with facts. This is an integer number, not a heavy 14 char key. Maybe it is a good practice in order to get better performance.
I will go on giving you feedback. I am still working on it.
Best regards.
Thank you – that sounds excellent!
Of course, surrogated keys are preferable (or at least numerical keys – I’ve decided against using them in my example for better understanding of the business logic behind it, but thanks for mentioning it ! )
Kind regards, Imke
This is very helpful!!! Thank you!!
Would you make video on youtube showing your steps. I will definitely subscribe to your youtube channel for such helpful PowerBI work!!
Hi there! I was wondering if I could apply this solution to my problem
I need to use a matrix to have 3 levels of accounts
and some of them have the same name but are from different balance sheet groups the big issue is how to sort them to respect the groups they’re from if I can’t sort them using sort by settings on power bi
Hi Gustavo,
this method doesn’t have any technical levels, so this won’t help you in a matrix visual. You have to use it in a table visual.
Not sure if I understood your request correctly, but it sounds to me as if you could create additional columns in Power Query where you define the desired sort order in the reports.
/Imke
Hi There,
I’m trying to implement this into a model I’m working on. It was functioning fine, I’ve since added some macros and a few other worksheets. The “magic” measure now just returns a blank.
Does anyone know what would cause this to happen?
Did you make changes to the data model?
Pingback: Abbildung von P&L-Strukturen in Power BI mit dem Matrix Visual und dem Zebra BI Visual – Linearis :: Self-Service Business Intelligence
THANK YOU SO MUCH
Mo