How to Build a Financial Report in Power BI

get 1 free month of Skillshare: skl.sh/3QwbRUQ - and you can check out my brand new Excel Bootcamp classes there too!
In this video I show you how to build a financial report that's easy to set up, allows for full customization, and doesn't rely on custom visuals. This technique is interesting for any table that needs a custom structure, no matter if it is a balance sheet or income statement.
Enjoy this video and subscribe to always stay updated on my favorite Power BI tricks!
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly [e-mail]
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
00:00 Intro
00:32 Why financial statements are quite tricky
01:28 Creating a template for the financial statement
03:05 Turning a table into an income statement
05:02 Adding the financial measures
08:12 Dealing with positive / negative values
10:12 Applying conditional formatting
11:44 Comparing different periods
13:14 Creating a financial statements based on a matrix
15:56 Adding a hierarchy for row items
20:08 Refining the financial statement
22:29 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO​
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZbin: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI​ #PowerBI​ #DataTraining​
#powerbidesktop​ #powerbitraining​ #powerbideveloper​ #DAX

[@brandongillins]

Just what I needed. New to PowerBi and Im tasked to full-stack financial statements at my employment. Funny your video dropped 24 hours after my assignment. Perfect timing!

[@texmexdragon2160]

Bas - I don't know what sort of mind reading ESP super power you have but every time you put out a new video it solves or addresses exactly what I needed at that time! We are starting to convert all of our financial statements from Excel to Power Bi and this video is EXACTLY what I needed. And as always, so incredibly well explained. Thank you!

[@markhenderson3771]

This is brilliant. I like the workarounds you display here (e.g. sorting with blanks). A lot of steps. I have watched this video multiple times.

[@andrew_hubbard]

This is great! I've created financial reports in Power BI before but I'd not created the different levels for dilldown so your guide was an ey-opener for me. The only problem I had with one financial report was when I created a seperate measure for each line item. The report used up all the memory and showed an error message when I uploaded it to the power bi service. In the end I created a single measure for the entire column that I needed to show, there were 10 columns in total. Your tutorial has now given me some more ideas for future reports. Thank you!

[@Robay146]

Wow this is amazing. You're always very precise & meticulous when making you're reports. Makes me change my whole thinking on how to make a report. Very advanced content as well. Seems like you always have a solution for every problem. Well, well done! 👏🏽👏🏽👏🏽👍🏽

[@HowtoPowerBI]

Thanks a lot Ro. I love to hear it helped :) Sorry to disappoint though- there are problems i dont have a solution to ;) but still searching…

[@cdamordakai]

I’ve been looking for this but explained for dummies!! Thanks!!

[@motazbs77]

You are amazing man. a simple and to the point way of explaining things. Keep doing your magic.

[@bekahsierra7313]

My job wants me to train on Power BI, and this is the first video I've ever understood. Thank you.

[@Tyraelxadrias]

Amazing video! Definitely going to try this on my next finance report!

[@juanignacioXI]

Astounding use of dax and native visual matrix!! Ty so much fort this Bas =).

[@zhengjieliang3638]

Thank you soooooooo much! I’ve learnt so much from your videos! It’s very useful in my work! Thanks for sharing! You’re great!!!!!!!!!

[@partymaschine92]

This workaround for sorting the line description by line number is simply ingenious!! Nice job!

[@HowtoPowerBI]

haha thx a lot- amazed myself that it works - but at the same time confused 😂

[@melissaphillips8506]

Thank you! Thank you! Thank you! I've worked so hard on data modeling and I've been stuck on the presentation of the data in the visual. Tremendously helpful. There is no way I could have figured this out on my own.

[@HowtoPowerBI]

Glad it helped Melissa :):)

[@pedrororiz9910]

mate, you are awesome. really. i think you found your true calling; teaching. kuddos for this amazing piece of information, super useful and the brilliant engagement technique you've developed. cheers from brazil!

[@ececphuah5868]

Amazing!! Thanks for the video, any future video on BALANCE SHEET ?

[@lubomirjagersky7327]

Awesome video, as usually I like your speed 😄 And the shortcut for edititng all the rows in query editor together was brilliant. Thanks a lot

[@es8560]

@Bas: This is a SUPER awesome video. I REALLY LIKE those tips and tricks which make the financial folks (BTW-I'm one of them) really happy when seeing it. Thank you so much for sharing your knowledges! And you have the magic power to explain it in a way that non-technical folks can also understand.

[@HowtoPowerBI]

Glad it was helpful! :) thanks for the kind words

[@inaxoxoxo2664]

Thanks! Such a lifesaver! I constructed a P&L for work using Power Query in Excel on Essbase exports. I had upwards of 300 measures! Then, I decided to migrate it to Power BI. However, I couldn't re-create the Excel structure that was so easy in Excel; using calculated measures and pivots. Until I came across your How To video! I had already created the Line Item dim so I was on the right track. But, it was your "Financial Value" measure that helped link it all together for me. Thank you again! Subscribed and liked! 😃😃

[@HowtoPowerBI]

Super happy to hear it helped :) Thank you for the support!

[@GenNextAnalyst]

Great tutorial! Thank you!!!

[@johndavie3924]

Fantastic, just what I needed!

[@ommm4169]

It's really really great, it would be awesome if we can also have the budget comparison as well

[@Akshita9614]

This video just saved my life and I surprised my collogues. Thanks a ton Bas. You are undoubtly the best.

[@HowtoPowerBI]

that is amazing! awesome 😎👊

[@marcocervantes9340]

I’m Data Analist this Chanel is in my Top 5 Business Intelligence, greetings for 🇲🇽

[@psz34]

So brilliant with the blank rows 😊

[@rajnishrajput2367]

As always awesome use of tricks and tips!!

[@brad7648]

This is genius! I was wondering how I could do this with my Business Central data. Great explanation; thank you so much.

[@cimagirl]

I will try this on weekend for sure! I was so distress with excel on big data using😊

[@jeanpaulishimwe3024]

Thanks for this amazing video. Well explained. would you kindly advice how can the difference of [Financial Value] - [Financial Value - Previous Period] be calculated? Thank you.

[@damn68]

You're simply the best 👌🏿 👍🏿. Thanks for sharing this knowledge. Microsoft power bi team should hire you.

[@hi55us2]

This is a fantastic starting point for a lot of finance related reports I've been running lately and a great mindset for building other reports. Thanks for the fantastic video! I'll definitely be saving it for future reference!

[@victormadoya1354]

I have tried following the steps but my values are the same for each line. I have followed your steps but still unable to crack . Pls assist

[@anuraja99]

@@victormadoya1354 Typically the issue of same value on all rows is because Power BI cannot determine the relationship between tables - check the fact table to dimension table relationship again

[@TheFreaKStrike]

Amazing solution! I've just recently done something very similar for a client PnL. Instead I utilised Calculation Groups (which I learnt from your videos btw!) and a dim_Lookup to get the same effect. My Calculations Items were things like Sales, Cost of Goods Sold, Gross Margin and so on, then in a matrix I could apply each measure in turn with format string expressions e.g. Actuals, Budget, VAR, % etc.. I used the lookup then to drilldown into the various row items. With a bit of work you can also get decent formatting, but your option is a bit more format friendly. didn't catch the whole video so not sure if calculation groups were mentioned. Your method is an excellent way as well for those that might not have access to tabular editor.

[@oscarrosalescorzo]

Thank you very much for this video.

[@kimberlyag1978]

This video is amazing. Game changer.

[@mateocasa6807]

Your are the best man!!!

[@charlesallen4821]

Really great presentation

[@smanzoli]

That's EXACTLY what I did 6 years ago... very good.

[@nbandeiras]

You are the Best. I am your Fan thanks for sharing your Knowladge with us.

[@HowtoPowerBI]

Ah Nuno, thank you!! Much appreciated:):)

[@yanelena8603]

Hello Bas! Thanks a lot for the great video. I would like to add YTD and PYTD columns and the very right side of the report. I've created separate measures for that, but when I add them to the matrix , they automatically go under "Value" and show the YTD beside each month column. Please advise how to solve this?

[@jameslennie90]

Thanks Bas - great video. How would you add year to date into the measure?

[@kvs123100]

Wooo woo woo.. what an awesome video! One of the rarest situation where we get what we wanted!

[@HowtoPowerBI]

Glad to hear this!

[@louism.4980]

Thank you so much Bas! :)

[@soundsofnature8690]

This is a very informational video! Instead of a Previous Month Column, how would I do a YTD Column? But would be based on the year and period that are selected. So period selected would be the max date

[@mr-rk394]

This is another level financial statement! thanks for the video.

[@HowtoPowerBI]

Thanks for watching!

[@brettwilson3795]

Great video Bas, definety something to add in with a change innnthe columns to show currddnt monthband yearvtondate instead. Thanks for the help.

[@Merrell2780]

Hi Bas, thank you for your Videos they are great! 👍😁 One Question, can you put that FORMAT / PosNeg thing around the SWITCH one time instead of several times for each item? Because every item has the same Numberformat. That would make the Formula much shorter. Nevertheless thank for that nice shortcut you have shown putting the posneg thing behind each item! 👍😄

[@dariodalferro2155]

thank you very much as always for your videos. How can we set up a multi-level reclassified that can work with a matrix without customizing the measure?

[@manjuiyer8983]

Thank you this is awesome video. Could you please do a video on allocations and displaying it in the format that you did above. In my company for eg we need to take the total marketing spend and allocate it every program offered at that location by the number of student starts. I have been able to create an allocation measure but when added to the table or matrix it does not show totals for each row.

[@GentilOliveira]

Great job!

[@vtn1700]

Amazing i will try to add the accounts based on the same Logik

[@carloschacha2026]

You are amazing buddy!

[@xXPejatTheCwalkXx]

I love your tutorials, they've been an incredible help for me so far! :) May I ask you how you record your videos? Camera, program? Thanks in advance!

[@parajf]

hi, Sales figure need correction

[@nithyakavitharan3029]

Great. Exactly what I wanted, Can you please add a link or video on "when clicking on a selected line item description", it should retrieve the corresponding information from another table..

[@nimithakurian7444]

This is awesome 👍👍

[@user-hf3pc8pq7r]

Thanks so much for the video! I did have an additional question - if you had multiple years worth of data and wanted to have an overarching header for each year with the months as column names below it, how would you associate the year to the month columns so when you horizontally scrolled the year headers would remain with the appropriate months?

[@ApexLegend-gu3bp]

I couldn't follow most of this. You are clearly at a level in Power BI that I am not even close to. I wish I had more experience/knowledge in this program. Just need to keep working at it.

[@caguamincaguamon2193]

Great video, it has helped me a lot! I have been scrolling through all comments but surprisingly it seems nobody else jumped into the issue I am facing: min at 14:10 of the video, it is suggested to include an IF statement to avoid an issue when sales = 0. But how about cases where sales is 0 but for example operating expenses are not 0 and we still want to show the P&L for a specific month, entity, country, product or whatever. Would be great to hear if others have had this issue, and what the correction to the DAX for "Financial Value" is or if any other setting needs to be adjusted to avoid this issue. Thanks!

[@felixhoigt8542]

Great Video, thanks. I am hoping you are showing a custom visual in the next video, such as Zebra BI.

[@user-nv6gc2eu5m]

Hi Bas, thanks a lot for this tutorial. Maybe you or someone can help with an issue I have - The Matrix rows for items that are referring to other line items (such as COGS, Income before Tax, Net Income, etc.) remain blank. I assume this is because the value on their ""components" (like COGS -labour) isn't actually selected when using the SelectedValue function for the summary lines. How did it work in the video and how can I overcome this?

[@ofentsekoolopile4811]

Fantasticcccccccccccc!!!!!!!!!

[@SivaKumar-fv6lk]

Hi Bas, Great Job, its really helped me lot to prepare our Income statement in PBI. Thank you so much.

[@HowtoPowerBI]

Glad to hear that!!! 😎👊

[@Lotaristo]

Started to do similar thing about a month ago - and it took a lot of time and efforts, and although i managed to complete it it was really complicated. But in your video there is almost all needed information presented (except that we don't see tables and connections between which may be hard for novices) in short and easy to understand form, and it's really should be useful for many people. I guess now i should return to my report and try to remake it using some of your advices (especially a thing with ISINSCOPE).

[@HowtoPowerBI]

Thanks for the feedback and all the best to your report :) hope it worked out!

[@prolf2024]

Thank you Bas

[@saeedalamry3857]

Thank you Very much, this is a grate!

[@HowtoPowerBI]

Glad you think so!

[@VuNguyen-do1mk]

Thanks!

[@nithyakavitharan3029]

Excellent video. Can I have the sample excel sheet to practice along with the coding to check it out

[@ahmadrais3585]

Great video

[@mirrrvelll5164]

Great and very useful video! Pity that PBI doesn't have actual P&L visual....it is one of the most important Business "things" in the world... Anyways I liked your approach, very good, similar to Sam's from DNA but still you covered it on detailed level. It is going to be of great use! =)

[@isjajo]

Thank you for creating these very informative videos, Bas. I really enjoy all the knowledge you share!

[@mastersenk]

Thanks for this video. Is there a way to actually drill through the measures that you create to get a breakdown of the figures that make up for example "Sales"? I noticed that you cant because measures can't be drilled through?

[@nithyakavitharant2013]

Hi Excellent explanation, is it possible to get details -I mean, when click on a item, it should display the details of a items.

[@user-sz9bi2nd7n]

Hello, sorry for the inconvenience. This video is great but, at some point do you connect the financial value table with the financial template? It is that I am trying to do it and the formula does not give me any error but it does not show me any value either. Thanks

[@normandtousignant1754]

Brilliant:)

[@Bruno20k.]

thank you Bas !!

[@HowtoPowerBI]

thx for watching 😀😎

[@darylellis7597]

Great video - THANK YOU! One of my challenges is the desire to have a blank row(space) between subtotals. The first part of your video using tables -seemed you were able to overcome. But somewhere at the end utilizing the matrix - the blank row between subtotals disappeared. Also, would it be possible to get a copy of the file for our better understanding? You hit a very needed topic!

[@MrNyamwanga]

Try this Link too might help. kzbin.info/www/bejne/m2PVYYGfjM2Gd80

[@azmanfc]

Hi You are the expert, but to complicated for normal users compare using excel with cube function which more flexible and easy understanding. But I will definitely try replicate the step you show us. Tqwm

[@yoginathbadadale]

Any video regarding day to day opening and closing balance calculation i am struggling with it

[@georgessabbagh3028]

great tutorial , it help me alot to build my financial report, however when i published the report, it got an error and the report was not able to be generated since i believe there was too many DAX calculations in the report; the error is "Visual has exceeded the available resources", do you have any workaround for this?

[@Phoenixspin]

Waiting for a follow up to this. You mentioned other techniques to do this.

[@seyiabayomi]

This is awesome and well simplified. Is there a material/accesible documentation for step by atep approach?

[@HowtoPowerBI]

Thanks David 😊 just this video

[@sutapaghosh2226]

Can we able to apply slicers for this also ? Kindly let me know if i have projects in my dataset can i able to slice the dashboard on the basis of projects ?

[@antoniodelpozzo2652]

Great!

[@faqruddinmohammad1411]

Great post. But If I click on Sales, COGS, Material from financial structure table.. I need full detalils of Sales data, COGS data with selected fields in another table. How can I achieve this ?

[@jornmazet]

Big fan of the chanel, you're my guru! One thing I'm struggling with in the Financial Value is the fact that I need to calculate for example Gross Margin (I see you have Gross Margin as a line in the data, but I don't). I have gross margin as a line in the report structure, and I have a measure for Gross Margin (sales minus cogs). If I add Gross Margin with it's formula in the financial value, it gives me no result. Can't figure it out... help? ;-)

[@vishnukrishnan3263]

Hello, Is it possible to create a line chart inside 100% Stacked Column Chart Visual? If yes, request if you can please explain the process. Regards

[@parajf]

can u make same report using field parameter, which is recently introduced by powerbi

[@SpursFanBos]

How do you highlight the line item names (row)? When conditional formatting, mine is applied to values only

[@WarooatDK]

is using tabular server, how would you deal with the sorting problem?

[@cabroncito29]

This is awesome! So I followed along, but ran into a problem when I tried to add my Prior Month column. I tried to add another SWITCH measure for Prior Month but it exceeded the resources for the visual? Since a Balance Sheet is supposed to be cumulative, how could I work around this issue? Because I can't just use "previous month". Current Month = all previous periods; Prior Month = all previous periods less the current month.

[@jensvancoppenolle7]

Hi Bas, thank you for this very informative video. I have a similar idea for a table in one of my reports, however for the visual you see around min 10 in your video, I would also like to get a Total value which is the sum of the above. How could i do this?

[@Lotaristo]

You can just add another row called Total in the end of your Excel-table sheet and then just add another measure to summarize all values.

[@elvirawehrli3841]

Great explanation and surely very helpful for all Power BI fans, many thanks for that. But I have an another problem: we have our financial values booked on separate accounts. These separates accounts are nested in different nodes all together in a huge hierarchy (many different hierarchies). Each hierarchy is maintaned at separate MDM application and will be changed regularly (additional accounts, new nodes or just movements of an existing account to the other node). Each hierarchy will be loaded into Power BI or a source of Power BI and must be presented at a balance sheet or P&L financial reports in Power BI daily. How can I quickly create such a balance sheet (or other typical financial report) with all the nested accounts with a possibility to drill down from the upper node to the each separate account all at a one sheet? This is the standard functionality of SAP BI and SAP BO Analysis. Is it possible to implement such reporting requirements in Power BI (and azure data pool as a data source) without SAP BI and SAP BO Analisys?

[@KylewithaHat]

great video, but spaces don't get deleted if you turn off the word wrap function

[@eziola]

AMAZING! Nice. Would you be able to add a Trailing-Twelve-Month's filter on the report so each month is always "TTM"? That would be a cool video or #short!

[@susasebastian]

You can calculate a TTM with the Quick Measures feature, look for the “Rolling Average” calculation and then create your measures according to your model.

[@gabrieldomke2519]

Thanks for the video! It has been very helpful. The only issue I found is when trying to format the subtotals, the conditional formatting doesn't allow me to select the highlight column to create the rule from the template made in excel. Does anyone know why this could be? Thanks in advance!

[@Sigma683]

I had the same problem. I solved it by creating the following measure and applying the condtional formating to the field: P/L Values Highlight = SELECTEDVALUE ( 'PL_Template'[Highlight] )

[@muhammadfirdaus2947]

Superb, how did you remember all those, hat off!!

[@HowtoPowerBI]

Haha :) made some notes before

[@MrDenisben]

great , but why you did not use a parend-child hierarchy for financial structure ? thks

[@geirforsmo8749]

Hi! Amazing video. I am trying your approach but I am really wondering how you connect to the disconnected table without using any virtual relation (using relationship for instance) or a real relationship. I had to use treatas, but the next happened were that I could not use the COGS sum of the 3 other COGS items. Can you shortly just how you did this? And an extra thing, I could not get the indent spaces in my visual even though I did as you did in the video. But thanks for a great video

[@victormadoya1354]

I have the same problem. Pls assist

[@fernandoguajardo6913]

You don't need a relationship, the template elements don't apply any filter context to the measures. The drawback of this approach is that you can't access details using drill through.

[@carlosalonso9511]

Hi Bas, ik begrijp niet hoe het kan dat je template tabel geen relatie heeft met je financials tabel; kun je uitleggen hoe dat werkt?

[@advrohitowhal9794]

Hi Boss, Want to show grand total in clustered column chart in Power BI. Is there any way?

[@Joe-rs2rd]

I was able to create the actual financial statement very easily in a matrix format. I could add time element for our actuals very easily. However, when i tried to add a budget value next to the actuals, the budget and actuals did not show up side by side in the columns. They instead show up side by side in the row categories. Any idea what the issue is. I set the budget equal to actuals to test and still had same issue.

[@Shixypeep]

I've not done it this way, and I'm struggling to see why you would. I've always used a trial balance report and a mapping table, with a mapping heirarchy. What are the benefits of doing it this way instead?