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

CTMatt,

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.

ChrisMatt,

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]

Matt AllingtonDid you turn off the “show rows with no values” option in the matrix? I forgot to do that in the video.

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

ChrisI 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!

Matt AllingtonI’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.

DenHello! 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!

Matt AllingtonSorry, 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. 😀

CFOSorry 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!

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

CFOMatt – 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

ChrisWere 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!

Corentin BodrosHi 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

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

Chris ChitemerereHi 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

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

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

Chris ChitemerereThanks 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

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

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

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

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

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

Matt AllingtonDid you turn off the “show rows with no values” option in the matrix? I think I forgot to do that in the video.

Christopher ChitemerereHi Matt, managed to sort my problem, thanks

Matt AllingtonWas it “show rows with no data”?

Chris ChitemerereYes it was thanks

Christopher ChitemerereExcellent demo. How can I access the Excel file for the raw data

ChristianHi 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

Matt AllingtonWell, 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.

JavierThanks Matt. This explanation is simply clever and really useful.

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

Matt AllingtonI see no reason why not.

Matt WhiteMatt, 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.

JoyceHi 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

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

JoyceYes! I got it working. Thanks to your book. 🙂

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

Matt AllingtonIt’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

JoyceHi 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

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

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

Robert WolfHi 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

Matt AllingtonI’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 🙂 )

NedaHi 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

Matt AllingtonYou 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

NedaThat 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

CFOHi 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!

Matt AllingtonThe 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

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

Matt AllingtonI’m not able to solve variations here. The best place to get free support is http://community.powerbi.com

CFOBrilliant 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!

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

Matt AllingtonHave you tried DIVIDE([Total Amount],[Total Sales])?

Frank Arendt-TheilenHi 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

Matt AllingtonYes, 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

SiddiqiHi 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

Matt AllingtonThis 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)

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

Matt AllingtonA 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

JohnGreat 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…

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

MarianaSuch 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!

Matt AllingtonI’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 🙂 ].

Robert WolfHi 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

Maksym MyronenkoAwesome!

btw, we can use ISINSCOPE instead of ISFILTERED in Magic measure, right?

Matt AllingtonYes, 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.

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

openus3My last measure is OK using ISINSCOPE function instead ISFILTERED.

Mahmood HumodahDear Sir

I need practical examples about how to prepare BS P&L CF from row TB with Power BI.

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