Power BI: The Income Statement
HTML-код
- Опубликовано: 15 янв 2021
- The income statement (P&L) can be a tricky report to build within Power BI and - as such - there is much conflicting advice online about the best way to achieve this. In this video, Chris Barber explains his preferred approach covering:
1. How to structure your data
2. How to create the data model
3. Using virtual relationships in DAX
4. Data Visualization
Chris also shows you how to create the report bridge. This allows you to easily explain variances and tell the data story about how you get from one value (budget / forecast) to the actuals.
You can download the the dataset and Power BI Desktop file shown in the video from github.com/MarkWilcock/lbag-o...
Simply the best! Your video and the downloads you provide are absolutely fantastic. Clear, concise and 100% on topic. Perfect.
Hi Chris, excellent work. I can't see the moment for trying the sample. For sure I'll be waiting for more videos. Thank you for generously sharing your "guru" knowledge with the community.
Hi Chris, major thanks and props for sharing the dataset and pbi file. I struggled to find a way to present the net/subtotal lines on PnL in power Bi and used to work around with excel. Your tutorial is easy to follow through, it helps me build my my first proper looking dashboard. Many thanks 😊
Thank you Chris! The file is well designed and the explanation is clear and concise. Pleased I could copy/adapt to my needs rather than trying to invent it all myself.
So eloquently and patiently explained and thank you for sharing the files
Tremendous job. Brilliant way to approach financials, much easier the typical template approach with can result in complicated formulas with long switch statements. Well done!
I've been looking at a few different approaches for creating a P&L report for my company and this is the model I will adopt. Thanks for sharing the desktop file. I've learned a lot.
Thank you Jeremy
Very Impressive. a well-thought-out report. Best of what I have seen so far.
Thank you!
I have watched a million youtube vids on how to create a P&L in Power BI. Seems every tutor has their own recipe and they all differ vastly. This one is awesome, easy to follow and apply the logic. Now that I understand the logic behind it, I can adjust the recipe to meet my exact requirements. Thank you!!
Thanks Jacques. Glad you found it useful & easy to follow
This is amazing and thanks for sharing your knowledge.
Very Impressive Tutorial. You are an amazing instructor.
Thanks Julie
Sir, you are truly a LIFE SAVER !! Thank you
Thanks Andre
This is great. I have been making good use of this template for over a year now. Thanks a lot
hello, I am wondering if you have some insight into what to do if your client adds some accounts
Mainly configuring your excel layout sheet to contain the newly created account with the right setting in it. Lemme know if that helps
this is exactly what i need. Thank you very much~
You're welcome 😊
This is absolutely amazing!
Thanks Ujwal!
This is an amazing Job!!
Thank you for sharing.. this very usefull for my job
Thanks George
Very helpful!!
The report and logic is awesome😊. Thanks for sharing this. Audio was slow rest all was great😊
Thanks Abhishek. I've updated my setup for recording videos going forward.
Thank you so much!
Awesome video!!!
Thanks Robert
Awesome - Very Handy
Thanks
Amazing video, best I have seen so far on how to create an income statement!
Only one thing is not working out for me. All the variables in my matrix have a positive sign, although the variables that are supposed to be negative have a negative sign in the mapping table. Any idea what I could be doing wrong?
Very useful, been looking for a good walk through like this.
Can see someone else has mentioned about the volume. It would also be worth either using the screen zoom earlier on, or reducing the monitor resolution before starting recording. Just makes it a bit squinty!
Thanks for the feedback. Working towards make more improvements to the videos going forward
Hi Chris,
Thank you for this.
Can you please elaborate of how the rows of subtotals like "Net invoiced Revenue", "Net Revenue", "Gross Profit" are calculated? Is it thanks to the Mapping table, and the relevant intersections, or is there some other calculation behind it?
Hi Chris - What's the ETL/transformation process used for calculating specific values in the Actuals (or Budget)? It's fairly simple to create a synthetic fact table for pre-calculated data, i.e., revenue breakdowns or cost types, but certainly not EBITDA or Profit, etc. Any ideas?
Thank You
Hi Chris! thank you it's really wonderful and informative video.
As I'm redoing the BI for learning purposes. For some reason the variance values + % (Budget / Forecast) are not appearing, even though my connections are exactly in your file.
can you help me please.?
amazing video, can u make a video to explain how we can make a data on query ready to use
Boss! Much respect.
Much appreciated
@@LondonBusinessAnalyticsGroup I've watched this legit 3 times (once taking notes, 2 times more passively). Legit, have all the Kimball books, etc etc, this has to be THE best income statement in Dax/BI around. So much love for making this.
Hi Chris!
Very nice video. I am making a P&L report right based on your ideas here. But I am struggling with a special case here. In my power bi report, the user can adjust the second level in the matrix by a percentage slider (1-100). That is the easy part. I just multiply all the elements within a switch statement with the percent factor corresponding to these fields. The same can I do for level 1 part too corresponding to the child level. But now the interesting and (for me) difficult part starts. I need to take this recalculated row fields (or perhaps only the sum level 1 field) and adjust all the other sum level 1 fields below since we now have changed a important value that must be taken into account. I have several things like creating a measure that new calculated value and then be used in the switch statement. But no, there must be some context mistakes or something in my approach. I will not work. Do you have any suggestion what I can do to make my report work? This drives me crazy and I need some guidance to come forward. Any suggestion would be grateful. Thanks
Great work! I have managed to replicate with my data but have some issues for Budget and Forecast. Year to date for Budget and Forecast shows the full year summarized and not the current months YTD figures. And for Quarter to Date it shows the full quarter. Not sure what I have missed or what's wrong since it works fine for actuals. Any ideas?
Very good result! I'm working on this goal too, but your solution looks more efficient. The audio unfortunately is very low.
Thanks for the feedback. Will increase the volume for the next recording
Thank you! Great explanation. I still suffer from this inefficient way to create these financial structures in power BI. What I'm missing here are lines with margins within the financial structure (like EBIT-Margin, and so on). Maybe you can add that in a later version.
Hi Daniel. You can do this if you include the margins within your income statement layout then use the SWITCH statement to pick the correct measure. The problem I currently have with this is that I'm unaware of a way to control the text size within the individual lines and usually I'd want the margins to be in a smaller font / italics.
@@chrisbarber639 Thank you. I agree. A smaller font looks better. I will let you know, if I find a solution for it.
@@chrisbarber639 I would like to know how too!
Hi Chris, Chris here :- ) This took me some time to follow and understoood the logic. But it really worked out, love it and it gonna save me loads of time in the future. One thing, i haven't had time to do yet. For instance, I'm probably done with november in the mid next week but the current month-formula it will show december now, which ofc dont have any data yet. How would you recommend changing that? Curren't month should be like last date with numbers. Great work keep it up, just the sound that are a bit to low!
Thanks Chris. Yes the current month column should change based on the date. You can either do this in your source table or use PowerQuery (m) to identify the current date.
TREATAS function is very well;BY the way, the voice of this video is too low
Thanks. I've adjusted the volume for future videos
Hi Chris, this is very helpful. May I request the actual BI model along with working files which can be modified to my requirements. Thanks a lot
Hi Chris, another great video, thanks very much. Just one question - in the date table, you have trues and falses in the current month, current quarter and current year columns - am I right to assume this is formula driven?
Can you please advise on including a prior month calc in the same view as the current month?
Excellent work on this project. What data es do you use to connect with PBI. Any chance it would be SAP.
Very good
Thank you Noushad
Very nice video. Just to add in your tutorial, the matrix of + and - is not necessary. You can do the association directly in PQuery
Thanks. Agreed you don't need + or - anything will do, but I prefer this when working through to understand the behaviour
@samueelXP How is the proceedure in Power Query to make the asociation without the need of "+" and "-" symbols?, you take as basis the "IncomeStatementLayout" or what is the process??
Thank you Chris. This was simply amazing. I have one question though on how you aggregated the total + and - in the net revenue for example. I am not sure how the value column in the mapping table finally adds and subtracts these rows together. Would you mind leaving any reference on how I could explore this further ? I hope my question makes sense 😄thanks a zillion
To be specific I mean the functionality behind the Income Statement Rollup Detail tab in your sources…
@@3mru7osny I was wondering about the same and realized it was aesthetic.
Also reading few of the other comments, Chris has already answered it in one of them,
sMKa: Very nice video. Just to add in your tutorial, the matrix of + and - is not necessary. You can do the association directly in PQuery
Chris: Thanks. Agreed you don't need + or - anything will do, but I prefer this when working through to understand the behavior
I believe, most G L Entries account for the + and - already and you need not worry much about it. So please clarify with your Finance team.
That being said, if it is an absolute necessary in your scenario, a simple change I would make is,
1) Replace + and - in the power query editor
a) Modify + symbols to 1's
b) Modify - symbols to -1's
2) In the Actuals table, create a calculated column to create a newValue which accounts for the multiplication and use it instead to calculate the measure G_L Sum :
newValue = Actuals[Value] * LOOKUPVALUE(Mapping[Value],Mapping[Sub Ledger],Actuals[Sub Ledger])
Hi
The solution doesn't actually use the "+' r "-" signage to compute the solution. Its more there for me as a user when working through.
The totals work because the actual figures are held in positive or negatives then it sums up the balance.
Hope that helps
Many thanks for the posting. Have you done a balance sheet and/or cash flow statement in Power BI
Thank you for watching. Currently I have not recorded videos or delivered any public talks on the balance sheet or cash flow. It is, however, something I'm considering as one of my talks this year. If I do, it will appear here on the LBAG channel. Feel free to connect on LinkedIn if you have an account as I will always mention upcoming talks and topics.
TOP!
Hey, great vid! any advice as to how to handle the addition of new accounts? I feel like it would be very difficult considering the layout and order stuff
Hi, Thanks a lot for sharing this. It is a very good tutorial for understanding how the stacked Income statement is prepared.
I am stuck on a point where I see zero values for the lines where I have blanks in the layout. Could you please help me?
Hi Jay. Thanks for the feedback. Where we have zero values I use the conditional formatting to change the colour to the same as the background. If you look at the Power BI file in the example, you can click on the table and see the conditional formatting I've applied.
this is very useful. Many thanks for sharing this.
I have a question on the Dates Tab in Excel. Can we just create a Date Table in Power BI and use this with all your other Excel data.
Yes you certainly can.
I use Excel as a data source in the example just for ease, but would recommend getting data direct from your ERP or data warehouse where you can.
This is very useful. Thank you. Instead of using current month, current quarter, current year, how would you go about choosing a specific period with a starting month and ending month or starting date and ending date?
Dear Chris. Thanks for your nice neat report. I got the following question: is it possible to choose a date for report? By now it is fixed on the current month/quarter/year. Is there a way to choose a March/1st quarter/March YTD when it's already June? Thanks a lot.
Hi Yelder. Yes, you just have to modify the DAX context. I'd recommend reading the definitive guide to DAX by Alberto and Marco if you haven't already
Legend
I'm trying to replicate the Income Statement layout, can you explain why there are gaps in the level 3 sort order.For example, the sort order goes 1,2,3,4 , 8 9,10, 17. What's driving this and does it really matter as long as it's in order. Thanks,
I really appreciate the wonderful work you have share, it surely is amazing. Is there a way to add a new measurement to calculate the variance Current Month vs Previous Year Month?
Hi Fares. Thanks for watching the video. Yes, you can create a prior year measure using a few different approaches such as using the DAX time intelligence functions (docs.microsoft.com/en-us/dax/time-intelligence-functions-dax) or using columns to identify the prior year in the date dimension if you have a non-standard company calendar, i.e., a 4 week, 4 week, 5 week quarter. I'm actually doing a follow up to this talk on the Balance Sheet in April (www.meetup.com/London-Business-Analytics-Group/events/275946620/) in which I'll be including prior year calculations.
Great Video and I was able to apply it on our own P&L. The only issue I struggle is to create the dynamic title. Nothing is said about it...Any assistance ?
Great you managed to apply this!
If you use SELECTEDVALUE in dax you can return the MTD, QTD or YTD value and then use this is a card visual.
Hope that helps
Chris
Hi there, Could you please explain the sorting for your level 3 order. I don't understand why level 1 and level 2 is sorted in sequence but level 3 skips a few numbers. I have been staring at the sheet for a while now and can't figure it out. Great video BTW!
Hi Chris, this is a fabulous piece, well done. I was trying to follow all you did, using your data but for some reason some lines are coming up as blank. The Net Invoiced Revenue, Net Revenue, Gross Profit, PBIT, PBT & PAT are all blank. Please do you know what I'm doing wrong?
Hi Yomi. Sounds like it's all the totals which are appearing as blank. The most likely reason is the table that bridges between actuals and the financial statement layouts is missing the general ledgers for the totals. For instance, revenue GL is showing against revenue but not against Gross Proift. In the balance sheet and cash flow videos I show an alternative approach using the switch statement which you might find easier to impliment.
@@chrisbarber639 Thanks for your prompt response. I agree the alternative approach in your balance sheet video is easier. Much appreciated.
Thank you for sharing the report. One thing I am not sure is that how to calculate the Gross Margin & EBITA. Much appreciated if you can provide a detail comment. Thanks!
It's done in a very similar manner to the other calculations. If you download the PBIX file using the link in the comments you can see the DAX logic and the table I've brought in. Hope that helps.
Watching the video, I have a question related to the amount of measures, wouldn't it be good to use calculation groups for time intelligence and variances?
Hi Torben. Generally I think there are better ways than calculation groups; there are exceptions such as using them for formatting. If I need to create a lot of measures with the same context, I.e., a year to date for all my measures I favour C# scripting in Tabular Editor to create them. There are other views out there, but I find this more effective.
Excellent video, thank you for uploading!
I'm struggling to hide the 0 values in the blank lines. Any tips?
Hi Tudor - one method would be to use the conditional formatting to change the text colour to white when the value is equal to 0
@@chrisbarber639 Tried that and it works for values, but not the subtotals. In my version, the blank lines sum up to 0, yet the conditional format does not apply to them apparently.
Depending on your version of Power BI, there should be an option when applying conditional formating to apply to totals and values. Its on the right hand side when the applying conditional formating box is open.
@@chrisbarber639 Thank you for your help! I had to change the setting from Values to Values and Totals and that did the trick!
Thanks for sharing
This is showing May IS, how can I see January or any other month
Should I put slicer or filter?
So I decided to show CM, QTD and YTD with the logic. In this approach the current month updates each month. If you want to pick months instead, as you mention you just need to add a slicer with month on the page or add into the filter panel.
Ciao Chris, bellissimo video...purtroppo non sono disponibili i sottotitoli. Grazie
In my data set the additional data fild is not calculating like Gross Profit , Total sales , EBITA can any body can help to fix this
Hi, how can I calculate in Actuals, % of Net Revenue in every Item in Level 1 Income statement ? Many thanks
Thank you so much Chris for making this video! I am fairly new to Power BI so there are some steps I don't know how to do. Would you please advise which of your videos I should refer to for guidance?
1. How do you get those bar charts at the top of the tables within Power BI?
2. How do you create the 'Manual Tables'?
3. What is that Rollup Detail table for? Why some are plus signs and others are minus signs?
4. How do you create those dynamic text at the top left of page?
Thank you in advance!
Selina
Hi Selina,
Thanks for the feedback.
This is around an intermediate level set of sessions, so will be difficult to pick up as a beginner. My advise would be to try and do a beginner course.
Here are some good options:
1) Dashboard in a day - these are often free
2) Work towards DA100 Power BI exam with Microsoft material
3) There are some great trainers out there (like Mark Wilcock) who provide training for businesses
I'd also check out your local area for user groups and see what events they have. Some of the world is now getting back to in person events again.
Hope that's helpful
Chris
Hi Chris, much appreciate the video and your approach.
I've got a minor problem. For some reason I've got a small glitch in my visuals -> It doesn't add upp (subtracts) the totals, for example I've got a 0,00 result for Net Revenue. Can it be the mapping?
Your help is most welcome
Thank you for watching and the comment.
You are quite right in your assertion that mapping is the most likely issue. If you check the mapping table, it might be you have no accounts linked to the mapping for net revenue.
If this doesn't work, you should be able to download the end result PBIX file and check back against this.
I'll also be live with the London Business Analytics Group on the 22nd February going through this income statement solution
@@chrisbarber639 Hi Chris, I made my mistake in merging the mapping table. I used the wrong column as a match. Resulting in null values. Thanks for your quick response.
I have however, one small detail. On my blank rows, there is a 0,00 value which I don't seem to get blank. Any idea? I used your formula.
@@ludovicvannistelrode9702 glad you got it resolved.
On my example I changed the format string in the data modelling tab and set the conditional formating for null values so it matches the background.
@@chrisbarber639 Dear Chris, thank you very much. You've been most helpful.
Amazing work Chris. One question: i can't replicate the treatas formule between actuals subledger and mapping subledger. What measures did you use for actuals and mapping subledger?
Thanks Frank. You can download the PBIX file from the comments to get the calculation. The basic premise is that treatas creates a virtual relationship. In the balance sheet/ cash flow approach I show an alternative calculation using actual relationships and then a switch calculation which you might find easier to impliment.
@@chrisbarber639 Thanks Chris, i used your formulas, but i still can't get the subtotals (gross margin, gross profit, ebitda) in the table.
@@chrisbarber639 I think the problem is in the mapping file. I created the confrontation (Gross Margin = Net Sales + Costs of Sales in rollup file), but when i unpivot and merge it in PBI, there is no result when i select gross margin, gross profit or EBITDA. At 14.06, i see that you do have results with fe Gross Profit
Hi Frank. As you mention it sounds like there is an issue with the mapping file. I'd have a go at the approach in balance sheet/cash flow as that uses physical relationships which are easier to work with if your unfamiliar with using the treatas virtual relationships approach.
@@chrisbarber639 Hello, Chris. What is the reason you're using 'treatas' and not just create real connection? Is there some reason for that?
impressive!!! Could you do the same for finnancial balance or Statement of cash flows!! tnx
We're doing a live session on the London Business Analytics Group meetup next week
Can you explain why at 7:55 on level 3 order, row 6 you jumped from 4 to 8? Also on level 3 order, row 9 you went from 10 to 17. Thanks!
Hi Alan,
You don't need to jump, as long as they are in order. When I first build the solution I had more levels and have simplified as gone through.
The rows he deleted were the "Not used" rows indicated in "_Ledger Mapping" excel sheet. The eleven "jumped accounts" in the "_Income Statement Layout" are the following:
Level 3 Level 3 Order
Billing error Not Used
Cost of Sales Adjustments Not Used
Falty Returns Not Used
Intercompany Cost of Sales Not Used
Other Discount Not Used
Other Returns Not Used
Price Protection Not Used
Revaluation of Other Discount Not Used
Revaluation of Volume Rebates Not Used
Revalulation of Price Protection Not Used
Volume Rebates Not Used
Dear Chris, is it possible that you guide on the Level 3 and Level 3 Order of the Income Statement Layout. Or could you be generous enough to explain on the Income Statement Layout for some of your subscribers benefit with a video. Your response will be highly appreciated.
Hi Benedict . Level 3 works exactly the same as levels 1 & 2. So for instance you have a level 1 such as revenue and the order for that is 1 as its first on the statement. Level 2 for revenue is operating revenue and intercompany revenue in the example. A level 3 example would be that operating revenue would be broken down into B2B or B2C revenue. Hope that helps.
great!!!
Thank You!
Looks like a useful video but I can't really hear. Is there another with louder volume?
Hello Chris
i got a lot inspirations from your video, can not say more thanks to you!
i have a question need you help:
regarding this measure,
Variance Actuals vs Budget, # =
// LOGIC: If actuals are greater than 0, then actuals - budget where positive values > 0
// LOGIC: If actuals are less than 0, then - (actuals - budget) where positive values < 0
var result
= if ([Actuals]>0, [Actuals]-[Budget], -([Actuals]-[Budget]))
return result
how about add a new column [sign] in 'IncomeStatementLayout'
for example:
when the category is positive, then current - previous
when the category is negative, then previous - current
category sign current previous diff # diff %
revenue1 + 5 4 1 25%
revenue2 + 4 5 -1 -20%
revenue3 + -5 -4 -1 25%
revenue4 + -5 4 -9 -225%
revenue5 + -4 -5 1 -20%
revenue6 + -4 5 -9 -180%
category sign current previous diff # diff %
expense1 - 5 4 -1 -25%
expense2 - 4 5 1 20%
expense3 - -5 -4 1 -25%
expense4 - -5 4 9 225%
expense5 - -4 -5 -1 20%
expense6 - -4 5 9 180%
Yes - can 100% do this rather than incorporate in the DAX. It's a good suggestion!
I have watched several RUclips videos and this is the only one which can help me to build my P&L report. Many Thanks, Chris!! But I got a question about how you bold the rows of the highlighted rows. I managed to create the grey background colour using cell elements conditional formatting. But I couldn't bold the rows.
Very Good Video, but the audio is very low, and try to enable subtitile.
Hi, I went through the video multiple times but couldn't find how the income statement rollup Excel sheet was getting used in DAX.
Have a similar requirement where on the report we need to show positive amounts but rollup should be based on the sign of the parent.
Any references are welcome or if someone could point me to the part in the video I might have missed. Thanks
I cannot find that in the video either and also want to show value without the negative sign but still want it subtracted.
@@dpv343 Maybe I missed something but will look at the video againPBIX file along with other video for chasflow he has shared
I just liked the entirety of the solution here why I wanted to take this approach.
@LondonBusinessAnalyticsGroup any reference would be helpful if you read this comment.
Hi Chris,
hope you are keeping well?
I am replicating your solutions for Financial Statements in Power BI. Starting from Income Statement (P&L) in my company.
We have the Nominal Structure table that captures all levels of the Income Statement. At the moment I am stuck trying to create the "income Statement Layout" required for your solution from our Nominal Structure.
Any chance you could share the initial table from which that layout was created?
Thanks a lot,
he has the data downloadable. Bu im doing the same as you, how did it work out for you?
Wow
Hi I am unable to sort Level 1 by Level 1 Order. I got the error message: "There can't be more than 1 value in Level 1 Order for the same value in Level 1". How do I resolve that?
Weng, probably you have the same information in more than 1 row in the Leve 1 with a different order in the Level 1 Order
FYI -Volume is very low for Canada- cannot hear - would you please fix - thanks
Link to download file is not working please check.
Thank you for this informative Power BI data modeling. I wish you could re-upload.. The Audio quality is not good.
Hi Jason. An updated 2022 version
is something im looking into it
Is it possible to configure your videos to see subtitles in Spanish?
Great! But I need it with subtitles pls! 💔
Downloaded files only contain text files no report no data?
You'll need to have Power BI desktop installed to open the PBIX file. You can download from here powerbi.microsoft.com/en-us/
@@chrisbarber639 Thanks Chris, I have PBI installed but when I downloaded the files the pbix is not showing when unzipping. I only saw a few text files. So I thought the pbix file is missing. This time it worked so thanks a lot! appreciate your help here.
best regards and take care
No worries@@DanielWeikert. Hopefully you find it helpful in building out your own Income Statement
@@chrisbarber639 can you give me your email address ? As I am very impressed with youf PBI Knowledage
May be you can write me an email
Hi Chris I need help visualising this with manufacturer accounts do you have an email address I can communicate with you directly?
Yes. Links are disabled in the comments but if you head over to my LinkedIn Chris Barber you can contact me there or follow a link to my website
The video audio is not heard well
😪
I can't hear the audio - too bad :(
Show show show.
Volume is too low
Thanks for feedback. It has been rectified in future videos
Not audible
The voice is too low :(
waay toooo loud
Very Bad audio
I realise it is at a low volume but quality should be ok if you turn it up. Will be increasing for next video.
Are you using the + and - in your grid in any kind of way? I am trying to get the values be negative and positive on certain levels of the hierarchy