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:
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
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:
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.zip
Enjoy & stay queryious 🙂