Build a P&L With Power BI - Excelerator BI

Build a P&L With Power BI

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.

Grand Total

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?

Percentages

Click here for Part 2 on Percentages.

79 thoughts on “Build a P&L With Power BI”

  1. 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

    1. 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.

    1. 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.

  2. 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])

  3. 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.

  4. 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]

      1. 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.

        1. 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!

          1. 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.

  5. 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!

    1. 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. 😀

  6. 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!

    1. 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.

    2. 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!

  7. Corentin Bodros

    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

    1. 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.

  8. Chris Chitemerere

    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

    1. 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.

      1. Chris Chitemerere

        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?

      2. Chris Chitemerere

        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

        1. Hi Chris,

          Good day to you!!

          Could you please help with the final solution you prepared ?

          It will be highly appreciated.. if you. Thanks 🙂

  9. 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.

  10. 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.

    1. 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.

  11. Christopher Chitemerere

    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?

  12. 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

    1. 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.

  13. 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?

      1. 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.

  14. 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

    1. Matt Allington

      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.

      1. 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?

        1. 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

  15. 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

    1. Matt Allington

      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.

      1. 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.

  16. 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

    1. 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 🙂 )

    2. 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

      1. 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

        1. 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

        2. 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!

          1. 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

            1. 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?

    3. 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!

  17. 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?

  18. Frank Arendt-Theilen

    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

    1. 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

  19. 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

    1. 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)

  20. 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?

    1. 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

  21. 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…

    1. 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.

  22. 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!

    1. 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 🙂 ].

  23. 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

    1. 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?

    1. I would also like to se Balance Sheet solution (can not find any example out there). By the way, great article – as always 🙂

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top