How to Build a Financial Report in Power BI
Vložit
- čas přidán 11. 07. 2024
- 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 support@datatraining.io
---------------------------------
⏱️ 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 CZcams: 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 - Věda a technologie
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!
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!
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.
I used your proposal one year ago to build a similar finance report and I "forced manually" the conditional formatting in the rows by overlapping a sahpe with some transparency to get somewhere little filled the main items. One year later I'm building another financial dashboard using again a very similar matrix with items list, measure using SWITCH function to allocate the right result every row...and I'm still facing the issue to format the items on the far left side of my matrix!!! This is a 2-years old video and PBI has not capabilities to format rows in a matrix.
Will keep looking to your channel to be informed the very first moment this new feature will be released as I'm sure you will inform us about it!!!!!
Thank you very much for your amazing explanations about powerBI!!!!!
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!
I’ve been looking for this but explained for dummies!! Thanks!!
You are amazing man. a simple and to the point way of explaining things. Keep doing your magic.
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! 👏🏽👏🏽👏🏽👍🏽
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…
My job wants me to train on Power BI, and this is the first video I've ever understood. Thank you.
Amazing video! Definitely going to try this on my next finance report!
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!
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
Fantastic, just what I needed!
Astounding use of dax and native visual matrix!! Ty so much fort this Bas =).
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!!!!!!!!!
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!
😃😃
Super happy to hear it helped :) Thank you for the support!
@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.
Glad it was helpful! :) thanks for the kind words
Great tutorial! Thank you!!!
As always awesome use of tricks and tips!!
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.
This workaround for sorting the line description by line number is simply ingenious!! Nice job!
haha thx a lot- amazed myself that it works - but at the same time confused 😂
Amazing!! Thanks for the video, any future video on BALANCE SHEET ?
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.
Glad it helped Melissa :):)
This video just saved my life and I surprised my collogues. Thanks a ton Bas. You are undoubtly the best.
that is amazing! awesome 😎👊
Your are the best man!!!
You're simply the best 👌🏿 👍🏿. Thanks for sharing this knowledge. Microsoft power bi team should hire you.
Thank you very much for this video.
This video is amazing. Game changer.
I’m Data Analist this Chanel is in my Top 5 Business Intelligence, greetings for 🇲🇽
Great job!
So brilliant with the blank rows 😊
This is genius! I was wondering how I could do this with my Business Central data. Great explanation; thank you so much.
Thank you so much Bas! :)
Really great presentation
It's really really great, it would be awesome if we can also have the budget comparison as well
I will try this on weekend for sure! I was so distress with excel on big data using😊
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.
You are amazing buddy!
Wooo woo woo.. what an awesome video! One of the rarest situation where we get what we wanted!
Glad to hear this!
You are the Best. I am your Fan thanks for sharing your Knowladge with us.
Ah Nuno, thank you!! Much appreciated:):)
Fantasticcccccccccccc!!!!!!!!!
This is awesome 👍👍
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?
This is another level financial statement! thanks for the video.
Thanks for watching!
Great video
Amazing i will try to add the accounts based on the same Logik
Thanks Bas - great video. How would you add year to date into the measure?
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?
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!
Great video Bas, definety something to add in with a change innnthe columns to show currddnt monthband yearvtondate instead. Thanks for the help.
Thanks!
Thank you Very much, this is a grate!
Glad you think so!
That's EXACTLY what I did 6 years ago... very good.
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!
hi, Sales figure need correction
Brilliant:)
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!
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
@@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
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.
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).
Thanks for the feedback and all the best to your report :) hope it worked out!
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! 👍😄
Hi Bas, Great Job, its really helped me lot to prepare our Income statement in PBI. Thank you so much.
Glad to hear that!!! 😎👊
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
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
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.
Great!
Thank you Bas
Great Video, thanks. I am hoping you are showing a custom visual in the next video, such as Zebra BI.
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..
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?
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! =)
thank you Bas !!
thx for watching 😀😎
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?
Excellent video. Can I have the sample excel sheet to practice along with the coding to check it out
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?
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 ?
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
Thank you for creating these very informative videos, Bas. I really enjoy all the knowledge you share!
Hi Excellent explanation, is it possible to get details -I mean, when click on a item, it should display the details of a items.
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?
is using tabular server, how would you deal with the sorting problem?
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? ;-)
Any video regarding day to day opening and closing balance calculation i am struggling with it
This is awesome and well simplified. Is there a material/accesible documentation for step by atep approach?
Thanks David 😊 just this video
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.
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 ?
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!
Try this Link too might help. czcams.com/video/e2r0NiWhSAk/video.html
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?
How do you highlight the line item names (row)? When conditional formatting, mine is applied to values only
Hi bas, I am struggling with adding the measures, like I have 3 separate measure in Salas, like Concrete sale, Pump sales, Ice Sales and Other sales, All above are separate measure and get the value perfectly in report when I am creating separate Measure of Sales to add Concrete sale, pump sales, Ice sale and other sales using plus(+) and then In Financial Measure I mentioned the “Sales”,[Sales] below the existing measures for the rest also, the output is shown as Blank in the Financial structure layout., can you provide me solution for my problem. Thanking you in advance
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.
Waiting for a follow up to this. You mentioned other techniques to do this.
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?
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
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!
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.
Superb, how did you remember all those, hat off!!
Haha :) made some notes before
can u make same report using field parameter, which is recently introduced by powerbi
Bas, somehow i could not get the added values like COGS inside my report....Any idea why? i can get the COGS inside a CARD visual correctly, but not inside the row
Good evening, I have a question, you added a column with 0 and 1 for highlighting, how did you do this? The thing is that this simple step is impossible to do in power bi, I tried everything
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?
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.
Hi Boss, Want to show grand total in clustered column chart in Power BI. Is there any way?
Anyone may help in the posneg variable max function would return single max value of the column that is 1 than how it returns negative values as well??