This is about an easy way to create typical finance reports like Profit and Loss using DAX that (unlike all other solutions I’ve come across so far) can be handled with very basic knowledge of this language like this:
The trick
The trick that makes my solution so easy lies in the fact that it requires no aggregation functions of the output-mediums like:
- pivot-tables: who struggle with asymetric logic and are not available in Power BI so far
- cubefunctions: who are not available in PowerBI so far
So we have to build the details as well as all aggregations into the solution as it is and don’t rely on/use any aggregation functions (This means for Excel: We have to turn off subtotals as well as totals in our pivot tables. It means for Power BI: Hurray! Finally a solution where the lack of pivot-tables doesn’t matter).
How to
The aim is to create a table/matrix with (account) details and aggregations into the rows and different slices of time-Intervalls or comparisons into the column sections. As for the columns, this will be covered by measures like [Actuals], [Budget], [PreviousPeriods], [Differences in all shapes…]. And – as the values in the columns should be the same – I’d prefer to use only one measure per column – that is fully sliceable and works on all (sub-) totals of course. … Ok – so some dreams later I found it:
MyMagicMeasure := CALCULATE([StandardMeasure], AccountsAllocation)
So you just wrap simple measures like Act=SUM(Fact[Amount]), Plan=SUM(Plan[Amount]), DiffPlan_Act=[Plan]-[Act] … into the CALCULATE together with the bridge-table as the filter-argument:
This is the many2many-technique in it’s simplest form (PostFromMarcoRusso). It all goes via simple aggregation on all accounts found in the filter context:
Our bridge-table “AccountsAllocation” consists of one account number per simple account and has multiple rows for the (sub-)totals – being all accounts that belong to them:
The ConsKey stands for the row in our report (1) and the AccountKey_ holds the account numbers that are going to be aggregated (many (for the sub-totals) and 1 for the account-rows). So all we need is this unique and simple aggregation on AccountKey for every row in the report – with a filter from the Reports-table via our bridge table to the DimAccounts, who then filters our FactTables: 1 -> many -> 1 -> many.
Report Definition Table
At the end of your filter-cascade stands the table that holds our account-schedule-definitions “ReportsAccountsLayout”. It needs the following columns at least:
- ScheduleName: As there will be all Schedules in one table (“AccountsSchedule”)
- A row identifier: That is the key for the Account Schedules (“AccountLabel”)
- List of accounts to be aggregated: Containing a list of all account numbers that need to be aggregated in that row (“ChildrenJustLevel0”)
- Description: To be shown in the report
- Index: To provide the sort-order or the report
Of course, all this is done using Power Query and can be fully automated. The ListOfAccounts might be the most scary column here, but don’t worry: No need to type in all the account numbers into the column manually. As long as your account schema definitions contain a pattern, Power Query / M will be able to break that down and create those lists for you. In this example I’ve taken the Contoso-database (LinkToDownload) which contains a parent-child-like definition. I will explain the details on how this is done in a later post (but you find the code in the enclosed files).
And this table also contains a couple of other columns as well that help formatting:
- Indent: where the number of blanks are calculated who go in front of the description
- Account/Total: Shows whether the row is an account or a (sub)total. This is used for conditional formatting in Excel and for drilling in- & out.
- Level: The level in the hierarchy
Conditional Formatting
The conditional formatting in Power BI is just very basic at the moment and I didn’t find it suitable for these kind of reports, but in Excel it works OK (this is a pivot-table with just our 4 measures):
In Excel as well in PowerBI you need to enable that rows/fields without values shall be shown. Otherwise the blank rows that structure our reports would be deleted. You will also notice that the Index-column (providing the sort-order) as well as the Level-column (providing the key for the conditional formatting) are turned to white fonds to make them invisible.
Of course: The price for this simplicity is that the signs will need to be shown.
I would suggest that you download one of the files and play around with it a bit to better understand what’s going on here. If there are particular areas of interest where you want me to provide further explanations, just drop me a note please.
Watch out:
When creating your report, the field “Index” mustn’t be aggregated. You either format is as a text field in your table or specifically set the summarize-option to: “Don’t summarize”:
Summary
We can use this metadata-based approach to create reasonably well formatted reports in Power BI and Excel who are fully interactive, work on the (currently limited) common denominator between these 2 programs and don’t require advanced DAX-knowledge. Consequent conditional formatting that could easily be triggered by the metadata (from the datamodel in the background) would further enhance comfort. Please vote for this feature here.
In this follow-up-post you can see how this technique can be applied to models where there are no parent-child-hierarchies but “good-old” accounts-groups-tables used instead.
Subscribers (who are logged in) can download the files here:
Excel: ExcelAccountSchedModelV4Upload.xlsx
New: PowerBI with data included: AccountScheduleReportsUpload_DataIncluded.zip
PowerBI with links to ContosoDB: AccountScheduleReportsUpload.zip
Sample data taken from here: https://www.microsoft.com/de-de/download/details.aspx?id=18279
Cleaned sample data in xls-format: SampleData.zip
Enjoy & stay queryious 🙂
Hi Imke,
Thank you for this post – it is a good inspiration for the report I want to create at my company. However before I start with this, can you please advise me on the following issue:
I have already created a multiple level hierarchy P&L (5 levels) in PowerPivot (as per Marco Russo chapter in his book) but unfortuantely it does not look as good in Power BI Desktop as in Excel.
This my scenario in short:
– I have chart of account with approx 300 accounts and I would like to create a short P&L with main categories like in your P&L shown in the post
– My Fact table (GL Entry) has obviously a list of all transaction with Accounts and Amounts
– For each P&L category multiple accounts have to be summed up (for example: Administration Costs consists of 10 accounts, IT Costs have 8 accounts and so on)
In your FactActuals table I noticed an AccountKey is linked to every StrategyPlanKey (I guess this is like a Transaction ID). Please correct me if I am wrong but what I need to do in my scenario is:
– clean and set up Chart Of Account with all necessary columns (Account Key, Index, Description and so on)
– in Fact table (GL Entry) create a calculated column with Account Key where Account Number will lookup for same Account in Chart Of Account and return the Account Key
– then play with relationships and finally create a report
– ….
Please let me know if my plan sounds good or if I misseed anything.
Thank you
Sylwester
Hi Sylwester,
things looking good in general, just some remarks:
– you can ignore the StrategyPlanKey, it is not used anywhere in the model (I was too lazy to clean up unused columns but might do that…)
– I wouldn’t expect that your current chart of account needs any additional work (especially if you managed the other model already). It corresponds to my DimAccount and all needed there is the AccountKey – which corresponds to your Account Number
– so no need to create an additional column in your fact table (GL Entry): The relationship to your chart of account will probably be there already (via Account Number)
– if you have your report table already (my “ReportsAccountsLayout”), all you need is the additional bridge table “AccountsAllocation”. I’d suggest that you build a mini-report with a handful accounts and one or two aggregations by hand to understand the mechanism and check if your model works in general as a first step.
You’re welcome.
Hi Imke,
I managed to get it done (all tables, measures, relationships are created and work as they should) and I really like the result. At the end I had litte problems with showing empty rows or rows with dashes but then I realized there is a function “Show items with no data” 🙂
PowerBi is a great tool isn’t it? I look forward to the time when Pivot Tables will be there as well.
Thank you very much for this article.
Kind regards,
Sylwester
Hi Sylwester,
congrats – very well done!
Thanks for letting me know and yes, PowerBI is totally awesome.
Many thanks for the example and insights! Also, thanks for bringing “Table conditional formatting improvements” to my attention; have added my votes this morning.
Thank you Tom & welcome.
Perhaps, finally, David Churchwards legendary “Cascading Subtotals”-Concept has a promising successor. I´ll give it a try. Thanks!
This simply doesn’t work, Is this for some mom and Pop spreadsheet with Like 5 entries?
Hi Larry,
are you experiencing performance problems here? Where do they occur? During load or when interacting with the report?
Hello, I am struggling extremely hard to understand what in the world you are doing here. I am Creating a Report and am running into a formatting issue as well. The best I can describe it is look at this video:
I would LOVE to use a simple formatting as you show, but even your workbook falls apart with any change at all.
Hello Larry,
I’m very sorry that this is causing you trouble and thank you very much for your heads up & the detailed description of your problem.
As it turns out, I have missed to mention an important point in my post: When using the Index-field, you have to turn off aggregations (Don’t summarize). I’ve included this in my post above now.
Pls let me know if you’re still encountering problems and I will study your model more closely.
Yes, That makes the format come back to where it was. I am still completely lost in what is being accomplished here though.
I have a table called : 00-COA = Full Chart of accounts from our Accounting system
I have a table called: 00-COASLicer = Categories, departments, Countries, etc…. It slices our chart of accounts to any requirement.
I have a table called” BSA00GLSummary = This is the Actual Data containing the debit and Credit amounts
What other table do I need? I simply don’t see what this report layout thing is doing. We have 4 thousand unique accounts in our chart of accounts. I couldn’t begin to imagine creating something like you have here as I simply don’t get it. Wouldn’t I end up with a table of millions of records?
What would the relational model look like? I am not figuring out even looking at your workbook how to Create this Report Layout thing.
00-COA —> 00-COASLICER —> Table???? —-> BSA00-GLSUMMARY
Yes, your assumption that the “Table????” will become very long, is correct. But that’s no problem for the VertiPaq-engine in PowerBI, which loves long tables. The foremost authorities on this field are using a somehow similar technique like described here: http://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/
The analogy between our models would be this: 00-COA —> Table???? —-> BSA00-GLSUMMARY
I don’t have a clear picture of your 00-COASCLICER -table right now and would need some further details in order to figure out how to incorporate this into my technique.
So I was right, This is only good for VERY small companies who have like 10 or 20 unique chart of accounts. There is no possible way you could maintain this with a chart of accounts in the hundreds or thousands.
No matter what way I have tried, this solution simply does not work with any type of large dataset. It is impossible to maintain or even understand how to make all this work on a chart of accounts that has hundreds or thousands of accounts.
Nice theory though, good solution for mom and pop businesses but NOT a solution for corporate reporting.
I’ve read an interesting article today on how to solve technical problems: http://brohrer.github.io/how_to_solve_any_problem.html
Happy to help if we could apply this technique here and you would ask questions to solve your challenge step-by-step.
Hello ,
Sorry for this long URL it is from my OneDrive,
This link contains two files:
1. COA = The Full Chart of accounts for all of our companies. The Main Index is the “Database Key” field which is unique for each account
2. COA Slicer = This allows us to Slice all accounts according to Various needs. It was also an attempt at creating a model as you describe:
So within the COASlicer there are only 3 fields I would like to group by and Aggregate data by:
1. Type = The Highest Level Category for Each account Like (Asset, Liability, Equity)
2. View 1 = Level 2 and Contains , Current Assets, Property and Equipment, Etc…
3. View 2 = Level 3 and contains Descriptions like: Cash and Cash Equivelent, Dues receivable , etc…
I would like a report categorized and displayed as the Image in the link called: What I m looking for.png
Let me know your thoughts
The First Question I have is what fields do I need in the new Table for reporting layouts?
I have the Fact Table: BSA00-GLSummary which is the primary fact table containing all the financial data I need. It has a Unique and Many DatabaseKey which is the exact unique key for each chart of accounts.
The Second Table which I relate to your DimAccount is the 00-COA Table which is the full chart of accounts containing almost 4K records. It is the Raw accounts coming straight from the accounting system.
The Third Table I have is the 00-COASLIcer table which I relate to your AccountsAllocation Table?? It contains my chart of accounts categorized and easily sliced based on whatever column is chosen, (For now I am only working on Balance Sheet Accounts)
So the 1st question is What does the fourth table I need have to have in it? Am I relating on my Type, and View1 and View 2 fields?
What would the layout look like?
Index Description
1 (Blank)
2 Current Assets
3 Cash and Cash Equivalent
But How do I get the totals?
Thank you for these details. Will need some time before coming back here.
I’ve also deleted the links you’ve provided in order to maintain anonymity.
Your modelling pattern is very familiar to me, as I’ve used it in my first multidimensional cube as well. It’s a very interesting variation to the pattern described in this article and I’m going to write a separate blogpost about it shortly.
Hello,
After reviewing the files do you think it is still possible to create the model you show here? It is so Overwhelming with so many accounts.
Sure. As I said I’m going to write a blogpost about it. Will use your data structure with randomized data items in it. But this will not happen before Christmas.
That is good news indeed. I look forward to your post…:) At first I couldn’t believe it would work, but after more testing I am thinking I may be able to make this manageable, Thanks!
Regarding Excel this is a brilliant approach. I’m quite impressed and a bit stressed!
Regarding PBI it can be simplified a lot using bidirectional filtering – neither ConsKey nor Magic Measures are necessary.
BTW, in my understanding fnRecColumnAsList equals Table.Column
Thank you, that sounds interesting!
Have made a wide circle around bidirectional filtering so far, but if it’s useful here, I’ll give it a try. Currently working on a simplified solution of this concept without Parent-Child-Hierarchy, so might include it there 🙂
Any update to this post? I still cant get it to work with our data. Are people using real small datasets here? I still don’t see this working in a true enterprise level financial reporting environment.
How can this be accomplished on a chart of accounts that is over 3 thousand records ?
Getting closer – with any luckk this week, but next week for sure!
Hi Can you provide the data source for this?
https://www.microsoft.com/de-de/download/details.aspx?id=18279 (also update in the article now 🙂 )
Hi I just subscribed and downloaded the files. However when i try to go into the Power BI tables or queries, it asks for my credentials and i am unable to see how the structure for the report is set up. can you help?
Hi there,
this sample file is designed to connect to a SQL-server with the Contoso-Database I’ve linked above. You need to adjust the name of your server in the first query “MyDatabase”.
If you want to connect to flat files instead, I’ve enclosed a zipped Excel-file with the tables that are listed under “ImportFromDB”: Just replace the code there with a reference to these tables instead.
I am trying to create a financial dashboard for a client in Power BI and struggling with how to set up the data. Would love to see the way the tables are set up and the queries that are used in this example file i downloaded from the link above so i can follow the logic through. thanks again!!
Hi nk,
I’ve now also included a zipped PBIX that already includes the sample data, so no need to connect to anything 🙂
Hi, is the new PBI matrix (still under preview) solving some of these issues?
Hi navi,
currently I cannot see how the new matrix will help here, just like a standard-pivot isn’t the perfect solution:
1) I cannot handle asymmetric subtotals (where within the same hierarchy-level, sometimes a subtotal shall be shown and sometimes not)
2) I produced empty/meaningless subtotals for non-balanced or ragged hierarchies.
3) Asymetric columns (like Actuals from last 3 years, but budget figures only from current year)
So if you don’t have any of these very specific requirements, you can of course produce nice reports using the matrix visualisation in PBI.
Hi!
I am trying out the PowerBI solution with links to ContosoDB AccountScheduleReportsUpload.zip, which I first had to correct to the database (warehouse) ContosoRetailDW when unzipped and restored the database. I also had to change the database settings Power BI desktop. Then I tried to refresh the data, the I discovered that both FactActuals and FactPlans failed during the step FilterEntity with following error message: Expression.
Error: We cannot convert the value “DimEntity” to type Number.
Details:
Value=DimEntity
Type=Type
The M statement it is trying to run is:
= Table.SelectRows(UnfilteredResults, each (List.Contains(Filter[DimEntity], [EntityKey])))
Can you help to solve this to make this run?
Hi Geir,
that’s really strange. Please check the query DimEntity like this:
In my DB (Contoso German) this field is defined as an INT and comes to PBI as a Whole Number even without applying a dedicated formatting step to it. If for some reason that’s not the case with your DB, please add a format conversion step in the query DimEntity.
Thats a cool trick. It took me quite a while to understand it. Bit if I am correct
your MyMagicMeasure is one sumif function.
Thanks Robert,
In my eyes, the whole DAX is a “Demanding Aggregate X” -concept 😉
In this case, we can actually skip the “magic measure” and continue to work with the “original” measures who work on the Fact-table, if we use bi-directional filter in PowerBI.
So the “magic” aspect of that measure is “just” that it allows to cross the m:n-connection between the tables that we cannot set in the model.
So setting bi directional and not using the Accountsallocation (bridge) table will have the same effect? I wonder because the conskey is essential for the reporting part. As there is no conskey – acc key relation in the dim account table
No, that’s not what I meant.
What we can skip with bi-directional filter is to wrap every existing measure into the CALCULATE(..MyMeasure.., AccountsAllocation)-command.
But we need all tables and keys for the correct “translation” for the aggregation, if we want to use a field from the “ReportsAccountLayout”-table in a report sum up all accounts correctly.
Makes sense.
Please i need your help,
how to divide or multiplicate KPIs using PL states (Example:KPI1=Total marketing cost/Total Sales) with using your magic allocations
Have a look at this article please: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/
Thank you! You great!
Wow, really clever approach. I am very impressed with the M Code throughout. Learning a lot from it.
With this approach is it possible to get a column of % of Sales?
Actually think I have worked it out, however you may have a more dynamic way of approaching it.
ActOdAll =
VAR indexValue = SWITCH(SELECTEDVALUE(‘ReportsAccountsLayout'[AccountSchedule]), “StandardP&L”, 3, “MarketingCosts”, 15)
VAR compareValue = CALCULATE(CALCULATE(CALCULATE([Act_], ALL(AccountsAllocation[AccountKey_])), FILTER(AccountsAllocation, AccountsAllocation[Index] = indexValue)), ALLSELECTED(AccountsAllocation))
RETURN
ABS(DIVIDE([Act_],compareValue))
Hi Tim,
please check out the KPI-post about it: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/
It has some parallels to your approach and you also find ways to show them in rows below the respective aggregated rows if you need that.
Cheers, Imke
Hello,
I’m just starting with Power BI and I’m trying to create a report similar to yours. I am trying to understand the logic, but I’m currently stucked as I don’t understand which kind of visualisation tool is your “Dynamic P&L reports” and how you create it?
I guess I still have a long road to go !
Thanks a lot !
F
Hi F,
it’s a simple table. Just drag the table-symbol to the report pane and add the fields to it.
But there are some advanced concepts included in this solution, so probably not the best way to start with Power BI 😉
Cheers, Imke
Ok, thanks a lot ! I’m still learning a lot and discovering everyday, I’ll have a deeper look in a few weeks when I’ll get more basic knowledge !
Great article – thank you very much for this useful content.
Just one minor question – I was really struggling with the decriptions. How did you manage that PowerBi recognises the blank values at the beginning of the decription in the table in order to show the structure visually?
With my tables, PowerBI does not recognise the blank values in front of the description. So I am really struggling with displaying the structure visually.
Thanks Linus!
Did you use the field “Description” and NOT “AccountName” in your report?
The indentation (putting blank values at the beginning of the description) is part of query “ReportsAccountsLayout” and in step “Description” it adds the blank values times the number from column “Indent” to it.
Although the blanks don’t show in the table view of the data model, they show in the reports at last (at least in my version).
Cheers, Imke
I have the exact same problem. I tired putting in a “_” before the whitespace but Power BI actually removed the “uneeded” whitespace between the “_” and the text. So “_ CostOfGoodsSold” became “_ CostOfGoodsSold” When i hover my mouse over the text it does recognize the intial text with the extra whitespace. Any ideas?
Sorry, no idea here unfortunately.
Did you happen to find the solution to this? I am having the same problem. In the data view it is showing the blank spaces before the report description, but in the report table it has removed the blank spaces.
After way too much searching for the solution to the indention, I found the answer! Go to the table formatting, select Values, and turn off Word Wrap. Works like a charm!
Awesome! Thanks a lot for reporting back here!!
Cheers, Imke
Dear Imke,
Thank you for this insightful post.
I just finished a course that gave me a complete introduction to the world of Power BI. The company I’m working for wants a P&L and Balance Sheet in Power BI. However, a lot of things from this post are still hard to grasp for me. What do you suggest I should learn in order for me to understand your post?
Thanks in advance.
With kind regards,
Dexter
Dear Dexter,
that’s really hard to tell, as I don’t know in which areas you actually struggle.
Please check out my overview of learning resources, if you haven’t yet: https://wp.me/P6lgsG-hY
Cheers, Imke
Dear Imke,
I downloaded the sample files from your follow-up posts and tried to follow the steps in the query-editor in order to understand the steps you have made. So I guess the area where the queries are made are where I’m struggling. Do you have advice by any chance?
Cheers,
Dexter
I’m working on a video course for this topic, as this technique is not suitable for beginners.
Will post link once it’s available.
I’m a beginner , please let me know once the video is published
Will do!
I’m trying to access the premium content. I registered and received confirmation that I can access premium downloads, but was not prompted to create a password and am, therefore, unable to log in. Are you able to assist? I am very interested in the models linked to this blog post.
Hi Stacey,
thanks for the heads up, have to check what’s going on here.
In the meantime, please use the “Lost Password”-link to set a password that gets you going.
/Imke
Hi!
I have used your ideas in a ssas/power bi solution, but I have a problem to be solve. My boss wants a power bi report where I can use a Matrix visual in a power bi report where he can drill down along the parent-child hierarchy. Basically there are two levels like subtotal and accounts and I make a hiearchy of these two attributes. So far so good but my problem is how to sort the top level in a way that matches the report structure by using the Sort By Column functionality. In a table visual you are using the index numbering. Have you or any other readers a suggestion how to solve this issue?
Regards Geir
Thank you for sharing!
Very helpful post. I was able to create an income statement for my company following this same approach withing power bi. However -I am wondering about adding another row for EBITDA (earnings before tax, interest, depreciation and amortization). How would I go about achieving this? Any insight would be greatly appreciated!
You add a row into the table “ReportAccountsLayout” where you want to see that new subtotal.
Give it an AccountKey of your choice and the ParentAccountKey of the next subotal below it (i.e. “Profit before Tax”).
Then adjust the ParentAccountKey of the rows in the report that shall be added within this subtotal instead of the previous subtotal (check for the AccountKey of the “Profit before Tax” item and change it wherever it shall go into this new subtotal instead).
MyMagicMeasure := CALCULATE([StandardMeasure], AccountsAllocation) = Genius
Hi,
You wrote:
“ReportsAccountsLayout”. It needs the following columns at least:
List of accounts to be aggregated: Containing a list of all account numbers that need to be aggregated in that row (“ChildrenJustLevel0”)
in what calculations that LIST take part ?
Pingback: Abbildung von P&L-Strukturen in Power BI mit dem Matrix Visual und dem Zebra BI Visual – Linearis :: Self-Service Business Intelligence
Pingback: Profit And Loss Dashboard Excel Template