A few weeks ago, Danielle Stein Fairhurst from Plum Solutions contacted me and asked if I would speak at her financial modelling meetup group (a topic of my choice related to Power BI). I decided to speak about creating a P&L using Power BI and DAX. I first learnt how to do this way back in 2015 when I met Derek Rickard. I was still fairly early on my DAX journey at the time and certainly didn’t have any experience with financial accounting using DAX. Derek shared with me how he built his P&L using Adventure Works. I have used these principles many times to help customers, and I thought it would be a great time to pass on that knowledge, and the skills I have learnt along the way on my blog.
It’s the How that Matters
If you have read a few of my blog articles, you would have seen a repeating concept appearing over and over; I like to share “how” to solve a problem rather than just provide people with the answer to a problem. It’s the difference between giving someone a fish and teaching them how to fish.
Video of My Presentation
Here is the video of my presentation. I used the same dataset used by Derek when I first learnt to do this myself. I then proceeded to create a presentation and demo using that steps people through the process of writing the DAX. I literally wrote each of these formulas, one at a time, and then placed the measure in the visual in Power BI to check that it did what I needed. From there I set about solving the next problem, with each step moving me closer to a working solution. I repeated the process over and over until the solution was complete.
Here is the video with the step by step demo.
A few people asked me for the workbook I used. I have not shared the workbook but I am happy to share my formulas. If you watch the video and study the formulas below, you should be able to learn from there. Each P&L is different depending on your source system and specific company needs. With that in mind, the formulas are not as important as the process of writing the formulas. But if you can see the formulas that I wrote, it should help you do this for your own data.
Raw Amount = SUM(GeneralLedger[Amount]) Display Value = SUMX(VALUES(Accounts[Report Sign]),[Raw Amount] * Accounts[Report Sign]) Amount Adds Up = SUMX(VALUES(Accounts[Sign]),[Raw Amount] * Accounts[Sign]) Magic Additive Total = VAR isHeaderFiltered = ISFILTERED(Header[Header]) VAR Result = if(isHeaderFiltered,[Display Value],[Amount Adds Up]) RETURN Result Running Total = VAR ThisHeaderID = SELECTEDVALUE(Header[Header Sort]) VAR Result = CALCULATE([Magic Additive Total],All(Header),Header[Header Sort] <=ThisHeaderID) RETURN Result Correct Display Amt = VAR CalcType = SELECTEDVALUE(Header[CalcType]) VAR Result = if(CalcType=1,[Magic Additive Total],[Running Total]) RETURN Result Total Amount = VAR CalcType = SELECTEDVALUE(Header[CalcType]) // should it be a simple total or a running total? VAR DisplayDetailCode = SELECTEDVALUE(Header[Detail]) // 1 if the detail should display and 0 if detail should be hidden VAR isSubHeaderVisible = ISFILTERED(Accounts[Subheader]) //we need to know if the sub header is trying to show itself VAR Result = SWITCH(TRUE(), isSubHeaderVisible=TRUE() && DisplayDetailCode = 0 ,BLANK(), CalcType=1 ,[Magic Additive Total], CalcType=2 ,[Running Total] ) RETURN Result
Turn off Show Rows With No Values
In the video, I changed the “show rows with no values” option in the matrix, to demonstrate a point, but I forgot to change it back. If you do that too, make sure you change the setting back, otherwise you won’t be able to hide the details for the blank rows of data.
Turn off Grand Total
Edit: 2 May 2020.
Frank posted a comment below asking me how to turn off the Grand Total in my matrix. It is a great question. It should be simple, but unfortunately it is not. You can see how to do it below.
But please vote to have this changed. You can vote here. https://bit.ly/2KSH7gs Voting is a great way to have your say on what should be improved in the product.
Comments and Thoughts?
What do you think? Did you know you could do this with Power BI? Do you have any tricks you use to solve some of the problems, perhaps in a different way?