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

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

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

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

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

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

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

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

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

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

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

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