How to Build a Financial Report in Power BI

[@Bowtieeffect]

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!

[@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!

[@VuNguyen-do1mk]

Thanks!

[@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.

[@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 😂

[@bekahsierra7313]

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

[@ececphuah5868]

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

[@motazbs77]

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

[@cdamordakai]

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

[@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 :):)

[@Tyraelxadrias]

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

[@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!!!!!!!!!

[@ersinustsoy4445]

You saved my life with this great video. Keep following you. Thank you very much for your time and effort

[@juanignacioXI]

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

[@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 😎👊

[@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…

[@aarushim9248]

idk if u know this but you're really charasmatic and i hope i will meet people liek ur personality irl pls god pls ur really nice like ur attitude its giving positive aura vibes already within 2 mins like i didnt come here to watch anythin gbut u explaning me stuff is smt i can listen/ watch 2 hrs straight like ur teaching a baby

[@marcocervantes9340]

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

[@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

[@ommm4169]

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

[@kvs123100]

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

[@HowtoPowerBI]

Glad to hear this!

[@johndavie3924]

Fantastic, just what I needed!

[@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!

[@nbandeiras]

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

[@HowtoPowerBI]

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

[@GenNextAnalyst]

Great tutorial! Thank you!!!

[@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

[@damn68]

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

[@brad7648]

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

[@mr-rk394]

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

[@HowtoPowerBI]

Thanks for watching!

[@mateocasa6807]

Your are the best man!!!

[@psz34]

So brilliant with the blank rows 😊

[@ahshew]

Excellent tutorial - even if a bit fast for a beginner!

[@cimagirl]

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

[@MrErlendFS]

top notch video. thx for sharing mate

[@kimberlyag1978]

This video is amazing. Game changer.

[@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

[@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.

[@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!!! 😎👊

[@LeonBorin]

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?

[@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!

[@jaumeayala9581]

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

[@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.

[@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.

[@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

[@JaweedLallmahomed]

Thank you for this interesting video, Can we have the link for the second part you mention in this video.

[@charlesallen4821]

Really great presentation

[@YazantPerezCardenas]

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

[@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

[@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.

[@jameslennie90]

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

[@rajnishrajput2367]

As always awesome use of tricks and tips!!

[@CommonSense-dd8cc]

thank you so much for showing how to create financial statements and all the tips and tricks and sharing the file. your lessons are gold. can the mapping template be used to organize financial ratios in a matrix/table? can the measures from the financials be reused in calculating fin ratios (ex.: totals assets/total equity, etc.)?

[@oscarrosalescorzo]

Thank you very much for this video.

[@brettwilson3795]

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

[@prernasaini2492]

Dear Bas, thank you so much for this, it's really easy and helping to create a financial dashboard, however when I am using the measures for COGS, Gross margin, Income before tax and income after tax, it's not showing any value in the visual as it's entertaining the row filter context, could you please help to resolve this query, thank you so much!

[@Aksheeta411]

How did you resolve your issue or are you still waiting for a reply?

[@smanzoli]

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

[@carloschacha2026]

You are amazing buddy!

[@nimithakurian7444]

This is awesome 👍👍

[@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?

[@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?

[@yoginathbadadale]

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

[@louism.4980]

Thank you so much Bas! :)

[@CommonSense-dd8cc]

this is fantastic.

[@TheGisellavictoria]

Thank you for the video, Its really helpful! I stuck at the last step for the percentage column, I use the same formula but the percentage didnt show on each line, its only show 100% on the total sales, while the rest are blank. Help

[@ThorBuhl11]

Would I Be able to put in eg a gross margin percentage inside the table?

[@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.

[@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 ?

[@sjetterboks]

Love it! Bought a book, but this is better. ❤ Any ideas how I could show a budget and real next to eachother in this format?

[@HowtoPowerBI]

thanks! :p which book? lol .. yes, just put another measure with switch on it that pulls in the values for the budget

[@sjetterboks]

@@HowtoPowerBIhaha, I should have ellaborated more. Power BI voor financials is the book. I got it to work! Thanks to your files.

[@HowtoPowerBI]

yey 🎉😁

[@smitahule-n5o]

Amazing Video but i one more requirement if i want to show the Web URL in table how to show it

[@WarooatDK]

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

[@CassieSpradlin]

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?

[@felixhoigt8542]

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

[@ShoharaSarwar]

Hi, I tried to utilize this method a few times but I am struggling in a few areas. For example, I have a list of line items from my data set that are the P&L line items. for the sales section there are a list of items that fall into sales, and I am able to populate the values for these items, but in my Line Item description table I have a line for "TOTAL SALES", that should be a total of the lines above, I can create the measure no problem, but when I include it in the financial value measure, it still does not show up. Please advise what I am doing wrong. Thank you and keep up the great content!

[@nareshlahoti2770]

same with me. The measure calculated from other measures did not show up in P&L. Like Gross margin = [sales]-[cogs]

[@muhammadfirdaus2947]

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

[@HowtoPowerBI]

Haha :) made some notes before

[@prolf2024]

Thank you Bas

[@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!

[@seyiabayomi]

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

[@HowtoPowerBI]

Thanks David 😊 just this video

[@saeedalamry3857]

Thank you Very much, this is a grate!

[@HowtoPowerBI]

Glad you think so!

[@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

[@vtn1700]

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

[@enizkaraosman]

Hi Bas, I have really bit shocked ! That's amazing! Actually, when such kind of finance people would like to see how our financial tables should be looked like and how to structure out them? That's wonderful. Actually, I would like to see how Balance Sheet can be structured and designed in Power BI as well? Is there any video for BS ?

[@HowtoPowerBI]

💻🤓 thx ! no dont have a vid on balance sheet

[@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.

[@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?

[@GentilOliveira]

Great job!

[@isjajo]

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

[@Phoenixspin]

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

[@NeMoooz]

Thanks Bas for the great video but I've a question, if I have a 1000 GL type, Do I have to post all of them inside the switch function manually? Is there another approach if I have all the financial entries with lots of data (GL account, posting period, Product Line, Type of post, etc.)?

[@farhanusmani211]

Let me know if you find the answer.

[@NeMoooz]

@@farhanusmani211 Yes I found the answer from a course within Enterprise DNA financial reporting course (paid one) the same approach as here but with using switch(true()) instead of listing them manually.

[@farhanusmani211]

@@NeMoooz Could you please share details if possible:🙂

[@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 ?

[@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.

[@vishalnasre1251]

Do you have any video for balance sheet like this one?

[@HowtoPowerBI]

not yet! hopefully soon 😉

[@vishalnasre1251]

@@HowtoPowerBI please please do one 🥺 Try for Bank Balance sheet as no video is available on KZbin for bank balance sheet in power bi

[@Bruno20k.]

thank you Bas !!

[@HowtoPowerBI]

thx for watching 😀😎

[@Borka-et6we]

Great video! However very simplified. In the real world I’ll guess you will have to use the actual account nr to join the chart of account and the ledger. How do you do that join when the gross margin and EBITDA does not have an account nr? Make one up?

[@awilksawilks1543]

Create separate measures

[@zchase6631]

Can i ask if the drill through details will work for this hierarchical setup?

[@fernandoguajardo6913]

They won't work.

[@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?

[@techfraser7880]

Question: I implemented the same with 17 columns (Actual, Budget, YTD, etc), however, this method affects the performance and it takes about 40 sec to load the visual. Is there a way I can optimize that?

[@wisdomwave9605]

Hey, @techfraser7880, I am having same issue. Did you find a solution?

[@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!

[@BN-SonOfTheSoil]

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

[@teodorVM]

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

[@MrDenisben]

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