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.
My Formulas
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. 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?
Percentages
Click here for Part 2 on Percentages.
I must be doing something wrong but simple here. When I click show items with no data I still do not get the Header Table pulling in any data that does not exist in the GL. For example the Net Sales does not exist in the GL so it will not show in the Matrix Rows. I get all missing data inside each Header that exists (Income, Cost of Goods Sold, Expenses, Other Income). I do not get any calculated rows. The Calculated row show in the Table if I break the connection between the Header Table and the Accounts Table but then calculations are incorrect.
Are you using the header table in your visual? That would be my guess. You must use columns from the header table in the visual
Matt, this is awesome you still help and maintain this still. I am using the Header Column from the Header table placed into the Rows within the Visuals. When I watch the video, it sure seems like I have the same structure you do but I must be missing something.
My suggestion when things go wrong is to start a clean visual on a clean page
1 create a new page
2 add a matrix
3 add header column to rows
4 add a simple measure, say sum of GLAmount to values
5 turn on show items with no data
Doing this eliminates all unknown issues. If it works, then proceed to build out the rest, noting if/when it stops working. If it doesn’t work, something else is wrong, although I can’t think what it would be off the top of my head.
This worked. Thanks so much.
Hi Matt first of all thank you for your well explained information. I have a small problem. I have created a matrix and in values section I have target sale and real sale and I also want to see success rate as real sale/target sale in % format. My problem is in grand total and sub total both as column and row it adds percentages together. for example in september index is % 70 and in october % 80 according to their values. but in total column it gives me %150 but I want it to calculate it like total real sale/ total target sale. How can I solve that problem .
Thank you
I cover the handling of percentages in part 2 https://exceleratorbi.com.au/building-a-pl-in-power-bi-part-2-percentages/
Nice presentation
Hi Matt,
Just want to thank you for this video, it has helped me immensely overcome few of the barriers I was facing building p&l reports
Thank you again
Matt, I’d asked you about this several years ago. Glad you’ve gotten around to you and nice to circle back to find you again! 🙂
Are you able to give further detail on what the following DAX line does:
“VAR isSubHeaderVisible = ISFILTERED(Accounts[Subheader])” //we need to know if the sub header is trying to show itself.
I’m not clear on what the DAX is trying to achieve and what the “Accounts” table is?
Any help would be much appreciated as it’s the only missing step I have before achieving the output PL similar to yours in your great video! Thanks
The Accounts table is a dimension table (Chart of Account). It has account code as a primary key.
That code determines if the subheader is showing or not. You can do a simple test measure using Adventure Works. Put Category and Subcategory on rows in a matrix and write that single line of code to see how the result varies in the visual.
Hi Matt,
Thank you for the response. I happen to be an accountant 🙂
I will look at the paid course.
cheers xf Perth
Al
Dear sir
please share the work book.
Hi Matt,
thank you for this brilliant video, which finally provided a solution for a P&L without needing to keep Power BI runnning for 45 minutes only to update the PL.
As you wrote before,
“You just need to write a simple DAX formula, eg DIVIDE([Gross Profit],[Net Sales]) and set that up to show for line item 4, etc. Hope that makes sense in the context of the Video”
When I try to implement this measure, I get an error message “The value for ‘Gross Profit’ cannot be determined. Either the column does not exist, or there is no current row this column.”, even though I set up my P&L exactly the same as you did.
As an alternative, do you know a measure for a new column stating the percentage vs. NS?
Thank you and kind regards
These are difficult problems, as you have no doubt discovered, and there is rarely an obvious “oh, just do this” answer. Here are some suggestions
1. Read this. https://exceleratorbi.com.au/debugging-dax-with-tooltips/
2. Buy an hour of my time, and I can help you http://xbi.com.au/Matt
3. Use InfoRiver https://exceleratorbi.com.au/?s=InfoRiver+P%26L
4. Ask for help at community.powerbi.com
Hi!
Very nice article. I am making a P&L report right now and I am using a similar approach. In my case I have a percentage slicer (1-100) that adjusts a specific the second level let us say all elements in “Other income and expenses. That is the easy part. I just multiply with the percent factor. But that only solves the visual part in that sub header. I could of course do the same with the level 1 part too. But now the interesting and (for me) difficult part starts. I need to take this recalculated row field and adjust all the other fields below since we now have changed a important value that must be taken into account. I have tried to create a measure that could be used to withdraw all row levels below. But I can’t be this work in the switch statement. This drives me crazy and I need some guidance to come forward. Any suggestion would be grateful. Thanks
These are always difficult problems, hence there is no “oh, just do this” answer. My suggestions
1. Read this. https://exceleratorbi.com.au/debugging-dax-with-tooltips/
2. Buy an hour of my time, and I can help you http://xbi.com.au/Matt
3. Use InfoRiver https://exceleratorbi.com.au/?s=InfoRiver+P%26L
Is it possible to show the sub-headers in sorted order by Amount while still showing the headers ordered in logical or data source order i.e. if I were to expand the Operating Expenses, it would be good to see the highest category of expenses show up first and the lowest last.
Sorry, I am not sure. You would have to test it.
Hi Matt
I am grateful to you for sharing this solution and the measures. Saved me from creating lots of measures. we have 4 levels of account codes. After creating my accounts table in excel in the format as per the video and tweaking the signs and the debit and credit amounts, I successfully created the P&L. Used the same technique to get budgets and calculate the variance and Var %.
Are there any videos on cash flow and balance sheets? 🙂
Mmmmm, cash flows and BS, the bane of my life. I don’t have anything but I have 2 on my list to work on. The problem is I’m not an accountant. I understand a P&L but I don’t really understand the others. Ken has a paid course at http://Skillwave.Training (I own half the company btw).
Hi
Thank you for the reply. I happen to be an accountant 🙂
I will look at the paid course.
cheers from Perth
Al
This is the BEST PowerBI video tutorial that I’ve ever seen in my life.
Thanks Matt, an impressive job.
👍
Sorry Matt, I re-write “Magic Additive Total” as shown on the video, then worked!!
Many thanks!!
Matt, thank you so much for all your efforts to load this detailed information. I followed your step then up to “Magic Additive Total”, I can get the data, however from “Running Total” onward I can’t the results, all the column show blank.
From your second video, header’s name “Header Sort” became “Order”, thus I changed its name but results are the same, i.e. no results, blank… Do you have any clue what is wrong???
Extraordinario existe muy poco material en castellano pero para quien quiera aprender incluso e idioma ya no es una limitante, muchas gracias por su gran aporte.
Extraordinary there is very little material in Spanish but for those who want to learn even and language is no longer a limitation, thank you very much for your great contribution.
This is a FABULOUS post/video. I used this, in combination with another post about hiding blank rows when you have ragged parent/child hierarchies and got a really good looking P&L using Power Pivot (not BI). I’ve encountered one problem though. If there is a value associated with a header level, and the user drills to the next level, instead of a blank label showing, the value is just not represented. So if you add up the sublevel values, it doesn’t equal the total, because the amount tied to the header isn’t shown. I cannot figure out how to show it, without showing all blank levels (with no values).
I’m not sure I fully understand and I am also not sure if there is a solution or not. These things are notoriously hard to debug (I guess you know that already) and I couldn’t say for sure without stepping through it in detail. If I were trying to solve this, I would be writing some test measures that verify the circumstances where the data is show or not shown, and try to go from there. I do offer commercial support for those that want my help, but I can’t guarantee I can fix it. What I can say is if it is fixable, I could be done in an hour. http://xbi.com.au/matt
Hi Matt,
My magic additive total column is just adding the income and the expense. I match everything you did up to that point. I tried doing different things with the signs but nothing worked. The formula is exactly how you had it. I’m not sure what’s wrong.
Thanks.
Hi Matt!
Great tutorial! I could implement this immediatly to get the sub totals correct according to our own P&L format!
Thank you!
Works like a charm.
I have used the same for creating a balance sheet. But how could I achieve adding a line for retained earnings ?
BR Tom
The principle is the same. You have to work out the calculation using measures, then set the header table to display the result in the right flow of the report. Sorry, I’m not an accountant so I can’t give any more direction than that.
Thanks Matt. Great solution.
How I can show the % in the same measure , in third row or any new ( Gross Margin % )
You are not the first to ask this. It depends on many things and it is not a simple answer other than you should follow the same principle as shown in the video already. I may do a follow up post in the future.
Just (successfully) used this. As with Pankaj, I look forward to seeing a version with Gross Margin % and Profit % … but I may have a go at doing this based on your principles. Many thanks.
Very good!!!
Hi Matt, This is very good and I could do the same for my report, after going through the video. But I struck, when I tried for the previous month comparative column @ Gross Margin and Operating profit. The total amount (running total) is coming only for the selected first column and is not coming for other comparative columns. I have used the formula of Previous MTH = calculate([Total Amount],PREVIOUSMONTH(‘Date'[Date])) , similar issue when tried for current quarter comparative column as well i.e CQTD = TOTALQTD([total amount],’Date'[Date])
Matt,
In one of the comments below – you said “You just need to write a simple DAX formula, eg DIVIDE([Gross Profit],[Net Sales]) and set that up to show for line item 4 ((GP%), etc. Hope that makes sense in the context of the Video”
When I do this and slicer by a single year I get the right number, however, if I add multiple years to my slicer, the calculation will sum Net Sales in every year in the denominator. Is there a way that I can write a formula that will take GP/NP for every year individually even with multiple years selected in my slicer? I’ve looked all over the net for this and I cannot find a solution.
Thanks so much.
Matt,
Thank you for the video. I’m almost finished, however, I am having a hard time merging the Gross Margin and Net Income rows I’ve made without Detail showing up. Are there any common pitfalls that you’ve seen? I don’t see where in the code for Total Amount where it commands to exclude detail.
I did not have to use the Magic Additive measure because my GL data already expresses negative and positive.
So I use “Display Value” which simply flips the signs. I don’t think that using this instead creates a problem.
So if I were to ask one question – what part about this result tells Power Bi to only display certain levels of code?
If DisplayDetailCode = 0 and isSubHeaderVisible = TRUE then all we get is a BLANK, no?
Thanks in advance!
VAR Result = SWITCH(TRUE(),
isSubHeaderVisible=TRUE() && DisplayDetailCode = 0 ,BLANK(),
CalcType=1 ,[Display Value],
CalcType=2 ,[Running Total]
Did you turn off the “show rows with no values” option in the matrix? I forgot to do that in the video.
What is happening is that the Running Total PLUS all the detail is showing and I do not get any blank rows which is what I am looking to achieve with regard to the detail.
I can jig with the SWITCH formula and get Blanks on GM and NO Detail. \
If I don’t jig with the SWITCH formula I get the Running Total with all that unwanted detail for my Gross Margin and Net Income.
What I am trying to understand is HOW the formula you provided accepts a number for Running Total for Gross Margin and Net Income, but hides the detail from Running Total.
FYI I just bought your Power BI book on Amazon and I think your online resources are fantastic. I’ve spent time over the past few days looking at your video and trying to learn DAX – it has been very helpful!
I’m sorry, but I just don’t know how to answer the question. It was 8 months ago that I did this video. Each time I do it I simply do it again from scratch and write formulas that work the way I need them to. I often write formulas that do not do what I want, too, but I then just focus on that formula, work out why it is not doing what I want, then fix it. Then I move on to the next peice – exactly as I showed in the video. And I always do it with the live model/data set up with a sample matrix/table so I can see what is happening. These things are very difficult to solve conceptually and I hence I can’t give you a clear answer. Sorry about that.
Edit: I guess it is likely to be the first line in the SWITCH
isSubHeaderVisible=TRUE() && DisplayDetailCode = 0 ,BLANK()
The SWITCH options are executed in order. As soon as it reaches an option that is true, it returns that value (blank in this case). So I suggest you test that line of code to work out why the rows that are showing against your need do not pass this test.
Hello! I’m new to PowerBI and I saw your video related to this blog post and I find it very helpful. I was able to use it on my report. Thank you!
Also, I would like to ask a question. I also have a financial statement and like your example, it has parent-child hierarchies. Some parent totals are the sum of its children but some parent totals has a unique formula.
For example: 2 levels
> Collections – sum of collections (no child)
> Investments – sum of children (3 children)
> Child A – sum
> Child B – sum
> Child C – sum
> Cash After Investments (no child) : Collections – Investments
How will I get the Cash After Investments? Thanks!
Sorry, I can’t solve this problem with the information you have provided. Building a P&L requires some skill in DAX. The video shows the process I go through myself to solve such problems and that was the point of the post. Each line in the header table either displays a sum, a running total, or a specific measure. You need to build the P&L using the process I showed and if the number you need doesn’t show, you need to manipulate the switch statement so that it does, first understanding why it doesn’t show and adjusting for that.
It’s not always simple. ?
Sorry to bother you again, but this is not a variation unique to me, I’m simply using the brilliant ormat that you’ve provided and I’ve created the measure for my Gross Margin %, assigned a Unique ID, calc # etc. but I can’t get any Switch formula to work with the Matrix. It’s blank every time. I’m not looking for free advice, I’d gladly pay, but since this is your unique solution, I was hoping that you might be able to follow up, particularly for those whom have really expressed their gratitude and shown interest in your work. I would be so appreciative of that since you’ve noted that you’ve learned from others, it would be helpful to pay it forward. Thank you so much in advance!
Please post a question at community.powerbi.com and include a sample of the workbook clearly showing the issue you are trying to solve. Post the link back here and I will take a look. These problems cannot be solved conceptually, you need to provide a sample working file containing data that shows the problem.
Matt – Thank you so much for agreeing to take a look! Here’s the link I posted to community.powerbi.com, which also contains the link to my sample workbook.
https://community.powerbi.com/t5/Desktop/Need-help-using-SWITCH-function-to-add-measure-to-a-matrix/m-p/1570375/highlight/true#M639346
Were you able to derive the solution? Gross Margin % is what I am working on now and I am completely stumped.
I tried a separate method where I used power query to filter Cost of Revenues and Revenues and then in each separate measure I summed them. I then tried to create another measure that would put Cost of Revenues / Revenues. It works when my filter has one year selected. But if I select multiple years it aggregates multiple years worth of revenues – ugh!
Hi Matt,
Thank you very much for the work you put in this video. It is indeed very useful.
I do have one question though. The PnL you presented is based on actuals (past) data.
Is it possible to make it a valuation model, where Revenues, Cost, and all the other accounts of your PnL, are measures themselves. So that you can make projections.
Example: Revenues = Units x Price x Growth rate
while Cost = Units x Cost per unit x Growth rate x % Productivity gain
Where in the end you’d be able to make your assumptions of growth, or productivity vary in a slicer.
I’m very kin to know whether you think it is possible or not ?
Thank you again,
Corentin
I see no reason why not. You would need.
1. A calendar that goes out into the future.
2. A forward pointing time intelligence function (measure) to do the calculations. You could do this with DATEADD()
3. A way to accept user input. You could do this with What If Parameters.
Maybe I will write a blog about this concept. No promises, but.
Hi Matt, I am trying to change the model to show negative values (expenses) as negative and thus get rid of the ‘Report Sign’ stage which is as follows:
Display Value = SUMX(VALUES(‘Accounts$'[Report Sign]),[Raw Amount] * ‘Accounts$'[Report Sign])
The Display Value is later required in the calculation of the Magic Additive Total.
I have heard some thoughts on this but i am failing to find a solution of how to remove the Display Value in the DAX calculations and replace it with the appropriate DAX measure so that expenses are reported as negative.
Kind regards,
Chris
I don’t recall the exact process I used (I do it from scratch each time depending on the need and I’m mobile atm). It looks like I had a report sign column. Simplicity you could just change that column to negative for expenses, and it should be done. If this column is then the same as the calc column, then you could eliminate it altogether and use the same column. I guess you could also eliminate the 2 formulas altogether, but you would need to step through the process as I showed in the video to work out what to do.
Thank you very much Matt for the prompt response, most appreciated. I will try it out and give you feedback.
Have you managed to do a Power BI Balance Sheet?
Thanks Matt. Reversing the Report Sign for expenses is the best option as one can switch back to positive reporting for expenses for companies that require reporting in this way.
Once again, thank you very much and most appreciated
Hi Chris,
Good day to you!!
Could you please help with the final solution you prepared ?
It will be highly appreciated.. if you. Thanks 🙂
This framework is fantastic. It provides the ability to include any values which are not contained within the natural dataset. This could be used to solve many of my existing DAX struggles.
It really helped to see your thought process as you worked through the problem.
We all learn from others. I learnt the technique from Derek. But the value I add is to teach others “how” to solve these problems. I’m glad it helped you.
Hi Matt,
First off, thank you so much for this presentation, it has been super helpful in putting together a P/L In Power BI. I had developed an additional way to get there, but this way is much cleaner in the end.
However, I am having issues with the Running Total Calculation. My magic additive total is coming up correct, but when I go to do the running total, my signs flip. I have attached a screenshot of the two totals side by side and the formula for the running total. Any help you could provide would be greatly appreciated.
I don’t see a screen shot. It is very difficult to understand the problem without seeing the model. The purposes of the video was multi-fold; to show that it can be done and also to show the process to do it. Everyone’s situation is difference so there is no single magic formula. You have to write each piece and make sure it works before moving on. I suggest you go back to the step prior, presumably where you were getting the correct results and then closely look at what is different with the next formula to identify the cause. If necessary, break the formula that is not working into its component parts so you can check what each piece of the formula is doing.
Thanks Matt, Great work. I see that the DAX calculation for Magic Additive Total above is different from the one shown in the video.
I managed to follow the process and reproduce with my dataset. I have one problem, the subheads have a (+) expand sign and are expandable which should not be the case. Have I missed something?
Did you turn off the “show rows with no values” option in the matrix? I think I forgot to do that in the video.
Hi Matt, managed to sort my problem, thanks
Was it “show rows with no data”?
Yes it was thanks
Excellent demo. How can I access the Excel file for the raw data
Hi Matt, thank you very much for that great video. It helped a lot to build a P&L with PowerBI in a easy way!
Is it possible to have an intermediate line ( overall costs) which just sums for eg. personell Costs, vehicel cost, room cost, depreciation,… ?
I hope I could clearly explain my whish.
Thanks alot and best regards,
Christian
Well, the principles I have demonstrated will work for your idea. You need to write a measure that gives you what you want. You need to add the sub total text into the header table in the location you need it, and then you need a new Header CalcType to determine when to show the new measure.
Thanks Matt. This explanation is simply clever and really useful.
Hi Matt,
I have just built the P&L using your video, very impressive solution. I am incorporating Ytd, Mtd and prior year and was wondering whether or not you were also able to reference a budget file in the same report?
I see no reason why not.
Matt, Thanks for the quick response. I have the budget incorporated now but have one final issue that is occurring when I try to incorporate the TotalYTD function. As soon as I select a date using my date filter the total lines (ie. running total) disappear from my report. I wasn’t sure at which point I should apply the TotalYtd formula ie, at the very start on the raw amount or within the Total Amount formula – potentially this is having an impact. Any pointers as to what might be causing the totals not to display would be much appreciated.
Hi Matt, I’ve almost reached the end. 🙂 I have a running total that is correct and working on the total amount now. When adding it to a table i get an error (in dutch, so losely translated): MddxScript (Model) calculation errog in Total Amount. In Dax comparing values with the type text with values of the type integer is not applicable. Use VALUE or format.
Now I have tried to change the calctype and displaydetailcode to numbers, and vice versa, but the error keeps occuring. Any ideas into what I am doing wrong?
Regards,
Joyce
This is a data type error. You are trying to do a numeric calc probably against a column of integers that is stored as text. It could also be a date column, in which case wrapping a VALUE() may fix it.
Yes! I got it working. Thanks to your book. 🙂
Hi Matt I had the same error and I believe it is due to my equivalent of your Account Sub-Header being text and the Header Type and Header Detail are numeric . Just wondering how to get around this if you can help?
It’s hard to give a generic answer. It depends on what you are trying to do in the formula. If you could post the exact line of code you believe is the issue, along with info about the data types, I may be able to be more specific
Hi Matt, thank you for posting this. I’ve watched the video and tried to create a P&L for our company. I just ran into a problem with the display value. The measure assumes that alle cost ledgers have costs and all profit ledgers has profit, but in real life it can happen that a cost ledger actually has profit on it (in bookkeeping this is portraited as a credit amount). For example exchange rate differences, this can be a negative today and a positive tomorrow.
Now I can change the report sign every time the total changes from positive to negative or v.v, but there must be a better solution. Can you help me in the right directon?
Thanks!
Joyce
Exceptions are easily handled in Excel but are always complicated in DAX. The trick in DAX is to not have any exceptions. Yes, my formula stores the sign flip at the revenue and expense level, but it doesn’t have to be that way. If you need something more granular, then you can store it at the chart of accounts level instead. Yes it has to be set up once, but at the end of the day it is business data that must be interpreted and stored.
Hi Matt, thanks for your reply. I think with this there is no easy way to fix it. The sign flip has to occur based on and the ledger account and whether the sum of the period and ledger is positive or negative.
So if the ledger is a cost ledger, but the sum is negative, then it is a profit and should be shown without a minus, but caclulcated with a minus. I will try to see if I can get a measure to do this. 🙂
I ran into another problem with the Total Amount, but I will use a new comment for that. Thanks again for taking the time to answer my qestion.
Hi Matt,
I got this done. It was an excellent solution. I even added some more formulas. For instance GP% and also aggregated my expense Headers and then added additional Calc types to the Header table. Then used the new calc types and formulas as additional choices in the switch formula and got the result. For the GP% I had to use the Format function to format it correctly.
I also noticed that there were extra blank spaces on my table. However after all was done I just went back and unchecked “Show Items with no data” and all the new calculated rows remained. Not sure if you mentioned that in the Webinar.
Thanks,
Rob
I’m glad you worked it out. As you know, my approach is always to teach the process rather than provide a solution – that way you should be able to handle the variations in your own scenario.
No, I didn’t go back and turn off that setting in the video (I got caught too 🙂 )
Hi Rob and Matt,
I agree, this is excellent!! So helpful! I am trying to add GP% of revenue as well as Net Profit% of Revenue. I think this is what you are referring to in your post above- would you mind sharing your formulas? Also, my OPEX (table below) needs to be sum of Labor and Non-labor not running total – any idea how to tweak this?
Account Sort Account
1 Net Sales
2 COGS
3 Gross Profit
4 GP%
5 Non-Labor
6 Labor
7 OPEX
8 DC
9 DC%
Thank you both!
-Neda
You just need to write a simple DAX formula, eg DIVIDE([Gross Profit],[Net Sales]) and set that up to show for line item 4, etc. Hope that makes sense in the context of the Video
That makes sense – i will try it out 🙂 thanks so much for your fast response! I watched your video 3 times today and I am finally understanding the concept. Again, i am so glad i found it!
-Neda
Hi Matt, Kudos to you, this was absolutely brilliant! I was able to set up my P&L last night, thanks to you! I have a placeholder Header line for Gross Profit Margin % which is simply Gross Profit divided by Revenue, but since Gross Profit is a subtotal itself, how would I do that calculation using DAX? Thanks!
The process is the same. Write the measure, insert a line in the header table, assign a unique ID and change the switch measure to make it appear
Hi Matt –
Thanks so much for responding! I’m not quite the master at this like you are, what is the measure to use, and how would I use switch?
I’m not able to solve variations here. The best place to get free support is http://community.powerbi.com
Brilliant Solution! @Robert Wolf Can you please share your calculations for GP% and aggregating your expense headers? I’ve been struggling with that for several hours. Thanks!
Great walk-through. I learned a lot from watching this a few times. I was experimenting with my own financial data and was able to recreate the measures you show in the video. I was trying to add another aspect to have a column that shows each line as a percent of sales, but I am not having much luck. I tried using SUMX and FILTER to create a measure for Total Sales and then I tried a measure that basically took [Total Amount]/[Total Sales], but I am getting Infinity and -Infinity results on every line other than the Sales lines. Is there an elegant way to solve this with your model?
Have you tried DIVIDE([Total Amount],[Total Sales])?
Hi Matt,
very excellent walkthrough and step by step solution to get the final P&L.
Question: My very last row of the final P&L matrix holds the subtotals label “Total” with no values. How can I get rid of it?
Thx so much for your very instructive video.
FrankAT
Yes, that is not intuitive, and I had to ask myself. Go to format, subtotals. turn on “per row total” and then turn off “header”. Why there can’t be a switch under “grand total”, I have no idea. Please vote to have this changed here https://bit.ly/2KSH7gs
Hi Matt,
It is Excellent. I can only think about it. i am a basic learner. The amount column you shows in one column, while i have Amount in 2 different column. i.e Dr and Cr column.
Can i have a link to download the excel file you used in this Demo or I can download the Power Bi file?
Best Regards;
Siddiqi
This workbook contains some content created by Derek, so I don’t want to distribute it – sorry. If you have 2 columns for Dr and Cr, then combine them together into 1 column using power query (flip the signs as needed)
Hi Matt,
Would the DAX have to modified if the data was presented in a table rather than a matrix?
And are there any specific reasons you used a matrix rather than a table?
A matrix automatically gives you an expandable hierarchy on the rows (+/- expand buttons) and also allows another dimension along the top (eg Years). There is no reason why the numbers would be wrong if you loaded the data in a table, it is just you wont get that type of layout
Great process Matt.
What do you think about using a Range for GL Accounts rather than the individual account number? This would allow your users to add additional accounts in between the ranges without the need for you to update the XL Workbook every time a new account was added. Something Like:
Act ID Start, Act ID Finish, ActType, Sign, RprtSign, HeaderID, SubHeaderID, SubHeaderDetail, SubHeaderSort
0, 50, Revenue, 1, 1, Rev, GrossSales, 1, 1
In the above, as long as your Customer added account codes between 0 and 50 (The defined range for Revenu accounts), you would not need to do anything to your model or supporting “Master” workbook.
It may complicate your DAX a bit as this may need to be a disconnected table, but to your Customer, could it provide a longer, less hands-on solution?
Just a thought…
Effectively it does work that way due to the header assignment. Each account is mapped to a header ID (Rev, COS, OpEx etc). When you add a new account, you need to make sure that the master data does the correct mapping. So this is a data management issue. You could do this in DAX with calculated tables or columns, but personally I don’t think this is the best place. Ideally it gets managed in your fin system when a new account is created. If it can’t be done there, you could use Excel to manage the rules (such as the number range) and then use Power Query to do the mapping for you.
Such an elegant solution! Thank you so much for sharing this video and walking us through the process, Matt. I’m one of those ‘demanding accountants’ you mentioned, and am also passionate about Excel. I’ve been struggling to find good use cases for PowerBI in my ‘reporting development’ world, but this demo has given me huge hope! I’ve got just the report to try out with this technique. I’m going to check out some of your other videos and books, too, as I really like your teaching style. Thanks again!
I’m glad you found it useful. Let me know if you would like any commercial support with what you are doing. My preference is always to work with people that want to learn to do it themselves. I can then focus on knowledge transfer and helping them to not make mistakes [I have already made most of the mistakes, so no need for my clients to make the same mistakes 🙂 ].
Hi Matt,
Excellent.
Just what I was looking for. Seems very doable.
I will give it a whirl and see if I can make it work with my data.
Thanks,
Rob
Awesome!
btw, we can use ISINSCOPE instead of ISFILTERED in Magic measure, right?
Yes, I believe ISINSCOPE Is a very elegant solution. I have never used it because I learnt to use other functions before this new function was released.
Magic Additive Total 2 :=
VAR isHeaderIsinscope = ISINSCOPE ( Header [Header] )
VAR Result = IF ( isHeaderIsinscope, [Display Value], [Amount Adds Up] )
RETURN Result
What about Accounts [Subheader] using ISINSCOPE? Values at the Header level and below will be shown?
My last measure is OK using ISINSCOPE function instead ISFILTERED.
Dear Sir
I need practical examples about how to prepare BS P&L CF from row TB with Power BI.
I would also like to se Balance Sheet solution (can not find any example out there). By the way, great article – as always 🙂