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?
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
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
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
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!
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 UA-cam 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
Same exact concept, still a two column table with parent and child, you just add more rows where the parent is a level 2 and the child is now a level 3. All those path functions will read it in properly
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.
download all templates (free of course) here: pages.ptarmy.com/download
Income Statement and say account grouping (rollup) is good, perfect for Power BI
Great content.
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
Great video!!! Thank you! Ok I now have 2 tables/jouirnals. actuals and budget - mapping hierarchy is the same. how to build a comparison?
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
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
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
Great! Really help me!!
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
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 UA-cam 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
How does this work with 3 levels? e.g. parent, child and childchild hierarchy
Same exact concept, still a two column table with parent and child, you just add more rows where the parent is a level 2 and the child is now a level 3. All those path functions will read it in properly
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.
help me a lot ! Thank you
Thank you for the comment!