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!
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! 😃😃
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
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.
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 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.
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 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 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
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!
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.
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!
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! 👏🏽👏🏽👏🏽👍🏽
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?
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
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
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
@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.
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?
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!
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
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 ?
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.
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).
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.)?
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?
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??
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?
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.
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.
Hi Bas, Actually for me the financial value column all the row items are showing same value. Would you pls assist as I have followed each step correctly.(Time Frame - 8:13 mins)
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!
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.
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
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?
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?
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
Desperately searching for a way to add a further level to the reporting i.e. a level 3 to show more details…tried using the OR function to check the ISINSCOPE (is it level 2 or 3) but it’s not working for me and I can’t find a way around it. Anyone any ideas?
Hi Guys, We have created a long P&L statement that works in the desktop BUT when we publish to the serve using a PRO license, it runs out of resources in the single visual. So my statement has 20 measures x 3 switch statements for Actual, Bud YTD and Bud FY. Has any one worked out a work around. My work around was to overlay 2 visuals....Regards Ashley
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 ?
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 ?
Hi Bas, Is it possible that in the *Switch formula when indicating formats as you did, they can also have values in percentages? Example: Two items with "##,0.0" and one with %? For some reason it doesn't work for me.
i have done the calculations the same way, however my gross margin isn't showing. Is it because the gross margin is not a description in my actual financial data?
Hi Bas, I can't figure out why I cannot sort my columns. I've tried everything and yet not working. I used your file and added lines and still nothing.
hmmmmm I'm curious - how is this easier than using xlookup or sumif in excel? One page with the formatting and formulas and one page with the raw data?
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?
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
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.
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?
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 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.
Hello! I'm currently working on formatting financial reports in a matrix visual on Power BI, as explained in the video for financial reports .I've classified certain rows with L1 and L2, but I'm facing an issue with highlighting specific row headers (like a3 and a4) within the same group (e.g., operating expenses is my l1 group and a3 and a4 are my l2 which comes under l1). Tricky part here is a3 and a4 are part of subgroup of l1 were in sorting order they comes in between the order which need to be highlighted.Is there a way to achieve this formatting within the matrix visual? Any guidance would be greatly appreciated. Thank you!
while you can highlight the values, you cannot highlight the row headers in the matrix visual. With a table visual you can highlight the row headers, but then you don't have the collapse / expand functionality. If this is a requirement you could look into custom visuals (like zebra bi)
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!
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!
My job wants me to train on Power BI, and this is the first video I've ever understood. Thank you.
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
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.
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 😂
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 :):)
You are amazing man. a simple and to the point way of explaining things. Keep doing your magic.
I’m Data Analist this Chanel is in my Top 5 Business Intelligence, greetings for 🇲🇽
Amazing!! Thanks for the video, any future video on BALANCE SHEET ?
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!
You saved my life with this great video. Keep following you. Thank you very much for your time and effort
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.
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!!!!!
Thanks!
This video just saved my life and I surprised my collogues. Thanks a ton Bas. You are undoubtly the best.
that is amazing! awesome 😎👊
Amazing video! Definitely going to try this on my next finance report!
I’ve been looking for this but explained for dummies!! Thanks!!
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
This is genius! I was wondering how I could do this with my Business Central data. Great explanation; thank you so much.
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!
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.
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!
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…
Astounding use of dax and native visual matrix!! Ty so much fort this Bas =).
Wooo woo woo.. what an awesome video! One of the rarest situation where we get what we wanted!
Glad to hear this!
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!!!!!!!!!
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?
I will try this on weekend for sure! I was so distress with excel on big data using😊
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
You're simply the best 👌🏿 👍🏿. Thanks for sharing this knowledge. Microsoft power bi team should hire you.
It's really really great, it would be awesome if we can also have the budget comparison as well
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
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 Excellent explanation, is it possible to get details -I mean, when click on a item, it should display the details of a items.
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
You are the Best. I am your Fan thanks for sharing your Knowladge with us.
Ah Nuno, thank you!! Much appreciated:):)
@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
So brilliant with the blank rows 😊
This is another level financial statement! thanks for the video.
Thanks 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?
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!
How did you resolve your issue or are you still waiting for a reply?
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
Excellent tutorial - even if a bit fast for a beginner!
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 ?
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.
21:40
I can't figure out, why, when adding the % value i can't also add the highlight option... It doesn't work for me - rest is fine
Would I Be able to put in eg a gross margin percentage inside the table?
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
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!
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.)?
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?
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??
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 video Bas, definety something to add in with a change innnthe columns to show currddnt monthband yearvtondate instead. Thanks for the help.
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.
Fantastic, just what I needed!
is using tabular server, how would you deal with the sorting problem?
top notch video. thx for sharing mate
Any video regarding day to day opening and closing balance calculation i am struggling with it
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.
Hi Bas,
Actually for me the financial value column all the row items are showing same value. Would you pls assist as I have followed each step correctly.(Time Frame - 8:13 mins)
sounds like you are return the same measure for each line item.. maybe double check against the example file (see download in the description)
That's EXACTLY what I did 6 years ago... very good.
Thank you for this interesting video, Can we have the link for the second part you mention in this video.
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!
same with me. The measure calculated from other measures did not show up in P&L. Like Gross margin = [sales]-[cogs]
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.
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
Great tutorial! Thank you!!!
Thanks Bas - great video. How would you add year to date into the measure?
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?
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?
Hey, @techfraser7880, I am having same issue. Did you find a solution?
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
Hi
For some reason, my switch function isn't showing values for some of the measures, please help
Desperately searching for a way to add a further level to the reporting i.e. a level 3 to show more details…tried using the OR function to check the ISINSCOPE (is it level 2 or 3) but it’s not working for me and I can’t find a way around it. Anyone any ideas?
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?
thanks! :p which book? lol .. yes, just put another measure with switch on it that pulls in the values for the budget
@@HowtoPowerBIhaha, I should have ellaborated more. Power BI voor financials is the book. I got it to work! Thanks to your files.
yey 🎉😁
Amazing Video but i one more requirement if i want to show the Web URL in table how to show it
Hi Guys, We have created a long P&L statement that works in the desktop BUT when we publish to the serve using a PRO license, it runs out of resources in the single visual. So my statement has 20 measures x 3 switch statements for Actual, Bud YTD and Bud FY. Has any one worked out a work around. My work around was to overlay 2 visuals....Regards Ashley
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 ?
Your are the best man!!!
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 ?
💻🤓 thx ! no dont have a vid on balance sheet
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?
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
Hi Bas,
Is it possible that in the *Switch formula when indicating formats as you did, they can also have values in percentages?
Example:
Two items with "##,0.0" and one with %?
For some reason it doesn't work for me.
I am having the same issue. Any solutions?
I was having this issue. The relatively new dynamic formatting is now a solution to this, thankfully
Dear Bas Bro- I am unable to do that after how to add matrix, can you help me
i have done the calculations the same way, however my gross margin isn't showing. Is it because the gross margin is not a description in my actual financial data?
Any particular reason why Number 4 is missing in Level 1 Sort?
Hi Bas, I can't figure out why I cannot sort my columns. I've tried everything and yet not working. I used your file and added lines and still nothing.
hmmmmm I'm curious - how is this easier than using xlookup or sumif in excel? One page with the formatting and formulas and one page with the raw data?
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?
Create separate measures
How do you highlight the line item names (row)? When conditional formatting, mine is applied to values only
Amazing i will try to add the accounts based on the same Logik
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
I have the same problem. Pls assist
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.
This video is amazing. Game changer.
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?
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.)?
Let me know if you find the answer.
@@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.
@@NeMoooz Could you please share details if possible:🙂
Hello! I'm currently working on formatting financial reports in a matrix visual on Power BI, as explained in the video for financial reports .I've classified certain rows with L1 and L2, but I'm facing an issue with highlighting specific row headers (like a3 and a4) within the same group (e.g., operating expenses is my l1 group and a3 and a4 are my l2 which comes under l1). Tricky part here is a3 and a4 are part of subgroup of l1 were in sorting order they comes in between the order which need to be highlighted.Is there a way to achieve this formatting within the matrix visual? Any guidance would be greatly appreciated. Thank you!
while you can highlight the values, you cannot highlight the row headers in the matrix visual. With a table visual you can highlight the row headers, but then you don't have the collapse / expand functionality. If this is a requirement you could look into custom visuals (like zebra bi)
I dont quite get how to can sum where there is no table relationship between your structure and financials table??? can someone please help?
Hi. Need some help. This No-breakup space is not working. What I am possibly doing wrong?