Creating P&L Statements In Power BI – Financial Reporting Example [2022 Update] #Finance

thank you hey everyone it’s Sam McKay from Enterprise DNA I’d say I’m going to take you through how you can create effective tables or a matrix of financial information so this is perfect for financial reporting in this case Profit and loss statement

because that’s what that’s the example we’re going to be working on today now historically I’ve received so many requests for for uh for examples like this it’s so common out there for All People based in

style="font-weight: bold; color: #1a73e8; text-decoration: none;">Finance areas or accountancy firms Etc or accountancy functions so I guess the the key thing I want to tell you today is that it is so good to be able to run this reporting inside of power bi power bi can produce this

type of reporting p l statements Balance Sheets other financial reporting it can it can showcase these results in a really compelling way now the example we’re actually looking at here this this was actually from an entire Workshop that was members on so it was for Enterprise

DNA members and it ran for about an hour and a half and I think well over 100 people came to the event live and what I ran through was how you actually create this from scratch like how do you actually take financial data build it into a model and then showcase it in a compelling way with all of

these other Advanced Techniques to Showcase information effectively one of the one of those examples was within this Matrix you can see here that we’re actually looking at a a comparison so we’re comparing actuals versus something right actuals versus in this case well it’s last

year but what you can also do within this table and white so good to do this in power bi is you can dynamically change what you’re actually showing inside a table really this is actually a measure but we’re actually determining that the measure in a dynamic way also you can jump between

time frames which is a gain wipe and you’re doing this analysis in power bi so great and you you know obviously it’s a lot harder to do this inside of Excel or at least showcase it in the compelling way in Excel and the other amazing effect and again why this is so much better than

doing this in Excel is the way you can utilize the dynamic filtering inside of your model and so you can select different areas of your report and what that does is it actually then filters every other visual that you have inside of your report page or inside your power bi model so all of these

things combined is really really effective right now I don’t have time to go through everything because the session uh the membrane session actually lasts for it was well it was over 90 minutes and so there was a lot to cover but what I do want to focus in on is just how you can build this

table how you can build this Matrix to Showcase financial information if you do want to watch the entire Workshop uh you can you just have to become a member um but if you want to have a look at all the other things that you want to go have a look at at the particular Workshop where it’s

located uh head to portal dot Enterprise dna.co and that’s Enterprise where you go and find the Enterprise DNA online and you’ll find it there inside one of the workshop series modules and so if you want to access that all you have to do is become become a member okay so now let’s

recreate let’s recreate this table and in doing so I’m going to show you how how we actually do it because there’s a few things that natively don’t get created and you need to make some adjustment some different formats but you’ll quickly see you’ll quickly see

that you can now do very effective reporting just like this for financial reports or Balance Sheets or Cash Flow statements p l statements Etc you can do it inside a power bi okay so I’m just going to create a new page here and I’m going to create a

matrix now we need to find all of our different filters right I’m just going to jump to the model quickly you’ll see here that we’ve got date filters at the top we’ve also got Financial categories so I’ve structured that in a lookup table and then this financial data

table well this is where all of the information all of the results right so let’s go and have a look at the table so you see here for every single month we have a result for each different entity and it’s a pretty detailed table it’s about 15 000 rows of information you’ll

see down the bottom here now we’ve got the Matrix now what we need to do is we need to start filling in uh the or selecting the dimensions that we want inside our Matrix so I’m going to go first we’ll grab my subcategory so this is the breakdown of Revenue and

expenses as you can see then I’m going to find my category and then I’m going to also find this this Dimension here which is financial statement list so this actually breaks things down uh you know line by line item right so really detailed then I’ve already got my measures

created so what I’m going to do is I’m going to put these into the values field you’ll see here that you can actually stack measures one by one and next to each other and the last thing I’m going to do is I’ve got my dates field here and I’ve got my financial

quarters which I’ve created and I’m going to put those into the columns so you’ll see that we have this breakdown here like this right but it doesn’t actually it doesn’t actually show it all in one Matrix or one all on one table currently we only have

Revenue expenses and if we want to go to the next level of the hierarchy they’re calling it here we would need to then select the double arrow down and then select it again and then you see here that you can actually jump between the three different dimensions you have inside

of the rows area now to me this isn’t good enough this isn’t good enough what we want to see is we want to see everything stacked together right and so what you can do is you go back to your first part of the hierarchy and you’ll see this combined double arrow down right so what

this does is it allows you to expand everything uh all down one hierarchy all done down one level in the hierarchy so if I select this you’ll see now that it all actually appears in the one uh actually this Net’s not everything we need to do it twice in this case because what it does

now is that it staggers it goes It goes through all of our different dimensions one by one right and actually indents them and so you see this this is very common look in Financial Statements and financial reporting right so just like that we can already get a pretty reasonable

looking Matrix of all the information that we want so you just need to make a few distance there but I want to do one last one because you’ll see here that I actually have them stacked right next to each other not just indentation I actually think it looks better like this where you have one

you have the subcategory category financial statement breakdown all sort of side by side in this case it’s actually I’ve actually used a different dimension financial statement list but it’s the same thing so how do you do it so what you’re going to do select the Matrix head

over to the format section head into I believe it is all row headers yeah so row headers right and you’ll see the stepped layout so if you turn off this tip layout you’ll see that it actually breaks it up and puts them side by side you get all of the different uh the the headers of each

of those different dimensions and I believe that this actually just lays things out at a very chronological way just like you probably would get in say an Excel pivot table but then you have all this additional functionality right okay so that’s basically all I wanted to show you and look

there’s so many applications of this right that’s why I wanted to I wanted to break it into one tutorial because you could use this across any type of information that you want to put into a matrix or a table I mean financial information is a perfect one but there’s many others

that you could actually do it for as well many other different yeah you could do it for any type of information and you’ll see here there’s just a really effective way to be able to Showcase it but then also utilize all of the filtering ability that you have you know you could even jump

in here multi-select and then this is all going to flow down to other visualizations you have and it can all be really well integrated into one report okay so as a reminder if you want to actually explore the entire because I know there’s a lot out a lot of you out there that are really into

financial reporting want to know how to do some power well um if you become a member of the Enterprise DNA there’s so much information so much content available to you and you know you can download this entire resource you can watch the entire Workshop it’s going to be way more

workshops coming out just around financial reporting and so if you do want to be able to attend and and also be able to access all of these different resources and content then then certainly explore membership is certainly uh something that you’ll benefit from very very confident in that

okay all the very best talk to you soon cheers hey everyone Thanks for tuning in to Enterprise DNA TV if you enjoyed the content covered in this particular tutorial please throw the CashNews.co a like it really helps us and we really appreciate it also don’t forget to subscribe to the

Enterprise DNA TV channel we have a huge amount of content coming out all the time from myself and a range of content creators all dedicated to improving the way that you use power bi and the Power Platform lastly check out Enterprise DNA’s website Plenty of resources and further learning

that you can access very easily all the best take care