Here comes some long awaited comfort functions for part 2 of my easy P&L series. In the first section I’ve presented the general principle on how to work with a structure using an accounts-group-table. Today I will present 2 alternatives to define the reports without specifying single accounts. So if a new accounts are added to the chart of accounts, you don’t have to adjust your report definitions: Just make sure that to fill in all the fields in your account-group-table and you’re ready to go 🙂
No need to specify single accounts
So you only need to adjust your report definitions if you add new group items. If that’s still too much, take the 2nd solution, which will even eliminate that requirement:
- Individual Account Layout: Just define each subtotal and determine for which subtotals single accounts shall be shown
No more specification of individual accounts
How to use it:
How to use Individual Report Layout
2. Ultrashort Account Layout: Further simplification of just defining the groups (hierarchy) that shall be shown (with option to filter on one of them)
No need to define individual group items
How to use it:
How to use Ultrashort Layout
So these 2 different layouts will both produce the same reports incl. all accounts – just like in the first example. So you can choose which layout-style suits you best – actually, you can use all 3 in parallel. You just have to make sure to grab your pivot-rows from the correct tables and in Excel to grab the matching measures, as they all have their own bridge-tables (which need to be used in the measures):
How it works
General principle is to use the account-groups-table as a lookup-table for the single accounts that belong to each group. And as we want to re-use the transformation-logic to the bridge-table, we aim for “just” transforming these shortened report definition-tables into the form of the “original” report definitions, where accounts, subtotals and everything else is on its right place already.
Simple as this sounds, the actual steps are quite extensive and I will not go into details here, as you don’t need to edit the code if you use the attached files. Just swap the existing import-tables to your data-sources. The only connection to my logic which you have to take care about is the accounts-table, which should be named “DimAccount” and its key-column should be named “AccountKey” (rename in the query-editor, if the name in your source is different).
DimAccountGroups
Key for the magic transformation is an account-group-table (DimAccountGroups) where every group has an adjacent sort-column, defining the sort-order of which the group-items will be sorted in the reports. This column will be used as a key-column for our transformations. These sort-columns all need a prefix (like “Key*” ) in order to be identified from the query-logic. (If you already have a account-group-table that you want to use and cannot rename the columns, create an intermediate query where you rename the columns accordingly before feeding into the functions).
Besides the group-pair-columns in your account-group-table there are some columns for the individual accounts (see the image below). If your existing columns have different names, you need to fill them into the “MyValues”-column. If you have more columns that you want to maintain, just fill their names into the “MyValues”-column as well. Otherwise the automatic transformation will not work:
Customization of Account Group table headers
Same goes for the “Report-definition”-tables:
Customization of Report Layout tables
Possible adjustments
If you should experience performance problems from the bridge-table getting too long: Just filter the import of your report-table down to those reports that are actually needed in your current file.
These translation tables are just for your convienence to adapt this model to your business solution. If you understand how to adjust the code, you can just edit the code and delete all these translation tables.
In the attached files, the sample data is already attached in the file. So you can instantly play around with it and follow the steps in the query-editor easily.
For subscribers only (you need to be logged in):
P&L Comfort Functions PBIXP&L Comfort Functions XLSX
I will share the extensive M-code on Github as soon as I managed this awe… resource …
Also check out the follow-up-post, where I’m sharing an easy way to sign-switching/unary operators (!!)
Enjoy & stay queryious 🙂
Thanks Imke so much, stunning tutorial!!!
Thanks Muhammad for the positive feedback 🙂
Hi! It seems really nice and good! I will definitely try your solution.
Still, a question: how to handle best in this case the currency conversion? Basically i have all my amounts in local currency (LC), and i need to see them either in LC, EUR or USD. Any best practice on where / how to implement it in your model?
BR, G.
Personally, I wouldn’t do this is the query editor (with Power Query/M), but with DAX (see some examples here: https://businessintelligist.com/2015/03/17/power-bi-tutorial-how-to-implement-currency-conversion-using-power-query-and-power-pivot/, https://www.kasperonbi.com/currency-conversion-in-dax-for-power-bi-and-ssas/.
So you add these tables to your model and adjust “MyBasicMeasure” (which was just a SUM of the facts table amount-column) to a basic measure that reflects the currency conversion before you continue with the elements from my method.
Otherwise for a PQ-solution you would have to create one new amount-column per currency in your fact-table and then build a switch-measure to reflect the selection. But I cannot see the benefits of such a method tbh.
Danke Imke!
Your ideas are really good 🙂
I am building up reports with parts of your P&L model. Really cool!
I had the same problem with the indentation being automatically removed. Google did not help.
The workaround I am using now is with Character.FromNumber (160)
Do not use Text.Clean afterwards!
#”Description” = Table.AddColumn(#”Changed Type”, “Report Description”, each try
Text.Repeat(Character.FromNumber (160), [Indent])&[AccountName] otherwise [AccountName])
Cheers
Thanks you Gilles for leaving this kind feedback and thanks for the tip!
Hi!
The logic here is working well for financial KPIs, where 1&1 makes 2.
But I have some non financial KPI that need to be calculated, averaged. For example the number of delivery points by month. Do you have an idea on how to implement this the best way in the model?
Cheers. G.
Hi Gilles,
not sure about your exact requirement here, but you can of course create measures with AVERAGE instead of SUM on the model.
🙂
Hi. These solutions are brilliant.
I have created a P&L and a balance sheet output using this approach and it looks very promising except that the accounts seems to follow a random order (i.e. COGS account is shown before Sales inside the ‘Gross profit’ group)
In the ‘Individual Report Layout’, how is the order of the accounts determined when ShowAccounts is set to ‘yes’?
I learnt this this happens in step InterlaceAccounts of the PowerQuery script of table IndividualAccountsLayout. I fixed it by adding an additional sort criteria. I replaced this:
InterlaceAccounts = Table.Sort(AppendAccountsToDefinition,{{“Index”, Order.Ascending}, {“IsAccounts”, Order.Ascending}}),
with this (adding last sort criteria):
InterlaceAccounts = Table.Sort(AppendAccountsToDefinition,{{“Index”, Order.Ascending}, {“IsAccounts”, Order.Ascending}, {“AccountKey”, Order.Ascending}}),
Now it’s working as expected.
Thanks for the heads up and great that you made it working!