Power BI - How to Make a Simple Income Statement Part 1
HTML-код
- Опубликовано: 22 авг 2024
- Financial statements can be tricky in Power BI, often people relapse back to Excel for its customization capabilities and familiarity. This tutorial helps explain one another way to use your general ledger data to generate a dynamic income statement, using PATH functions in DAX. Stay tuned for more videos on making financial statements, financial analysts and accountants in all parts of the workforce should embrace the capabilities of financial reporting in power bi!
------------------------------
Free downloads of templates and data used: pages.ptarmy.com/download ✌️
------------------------------
As always please subscribe, like or comment with any more ideas or feedback. It helps keeps me going.
download all templates (free of course) here: pages.ptarmy.com/download
Income Statement and say account grouping (rollup) is good, perfect for Power BI
Realy impressive and so helpful, I have followed the steps but I am stucked in the sort step, the message keeps appear "We can't sort the 'Level 2' column by 'Sort Order'. There can't be more than one value in 'Sort Order' for the same value in 'Level 2'. Please choose a different column for sorting or update the data in 'Sort Order'."
Is this because the embty cell in level 2? but in your vedio it works, i am realy stuck in this step, please help.
Hi Ptarmy, I followed all you video, this stuff is informative and detailed and too easy to follow, i am stuck in the Path item and it say, " Each value in 'Income Statement'[Child] must have the same value in 'Income Statement'[Parent]. The value 'Operating Expenses' has multiple values", This income statment is for Readymix concrete model and i have multiple operating and non operating items, How to address this issue, can you please assist me please
Is there a way to sort the second order (Level 2) in a way apart from the normal alphabetical order?
Yes I would create another virtual table with two columns, one your level 2 unique values and the second the sort order in integer, relate the table in, create a calc column using RELATED() and then in model view do the sort by
Hi, Awesomes work @ptarmy, Your video are impressive and easy to follow, I followed step by step and stuck at Income statement Measure, were my gross profit and Net profit are giving me same value rest of the item of Income statement are calculating properly, Can you explain how to fix this problem
Hey Mohammed, I try and make it easy to follow but realize there are a lot of bits of information and sometimes some linkages may get missed. Were you able to download the template and look at the Dax for the income statement measure? I think using the template to copy the syntax exactly is going to be the fastest route to solving your problem
@@Ptarmy_ the power bi template for this video is missing
Great! Really help me!!
help me a lot ! Thank you
Thank you for the comment!
This is much easier to get the subtotal. However, still cannot remove the repeated subtotal (eg. Gross Profit) by using the measure @ 12:02. My data model designed is Level 1 from Header Table, Level 2 from SubHeader table, Level 3 from COA table...🥲 any other measure to suggest?
Will check shortly and see if I have any tips
Any workaround on the Sort by column? It doesn't allow me to sort despite only having unique Sort Order number per each Level 1 for instance. Please help. Thanks!
Sort by is notoriously finicky, only other thing to check is ensure no blank values in that column you are trying to sort
How does this work with 3 levels? e.g. parent, child and childchild hierarchy
I'm having trouble with this method: It keeps giving me an error message about how a certain value in the parent column must also exist in the child column. However, there are no NULLS or blanks in either column. Additionally, I have tried putting the "missing" value on both the child and parent (expense - expense) but the error is still persistent. Any reason this could be ?
In addition to this question, I was wondering how you were able to create a one to many relationship from child to account if the income statement is not a unique value to the general ledger table
Naomi - good questions and a common spot to get stuck… basically in order for PATH technique you need to add that extra record at the end that is the same child and same parent value. In this case the text value “income statement”. If you leave that out you indeed will get that error. Sort of challenging to help over RUclips comments but I encourage you to download the free template, or the template on my latest video “pro forma financials” that uses the exact same path technique and reverse engineer it