Forum Discussion
Build Balance and P&L
- 4 years ago
I modify your final code:
IF ( MAX ( 'Report Balance'[Betreft] ) = "Balance", SWITCH ( TRUE (), MAX ( 'Report Balance'[Sortgroup] ) = MAX ( 'Report Balance'[Group 1] ), [GROUP_1], MAX ( 'Report Balance'[Sortgroup] ) = MAX ( 'Report Balance'[Group 2] ), CALCULATE ( SUMX ( ALLSELECTED ( 'Report Balance'[Sortgroup] ), [GROUP_1] ), FILTER ( ALL ( 'Report Balance' ), 'Report Balance'[Subtotal?] = 1 && 'Report Balance'[Sortgroup] < MAX ( 'Report Balance'[Sortgroup] ) && 'Report Balance'[Sortgroup] > Pre_Group2 ) ), [Total Balance] ), IF ( MAX ( 'Report Balance'[Subtotal?] ) = 1, SUMX ( FILTER ( ALL ( 'Report Balance' ), [Betreft] = "P&L" && [Sortgroup] < SELECTEDVALUE ( 'Report Balance'[Sortgroup] ) && [Subtotal?] = 0 ), [Total Balance] ), [Total Balance] ) )Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.Best Regards,
Community Support Team _ Janey
Thanks again for all the help you give me!
I managed to modify the measurement myself so that it can be used in Power Query.
I would love to put this forum post on resolved 🙂
But i have two more questions 🙂
Now that we have calculated the "Net Income" (-100,636.50), I would like to add it to the "Result" (-6,479,770.48) in the balance sheet. So that this total comes out to 6,580,406.98. This is where the balance sheet comes into balance (Assets 60,310,820.79 = Liabilities 60,310,820.79). Is this possible?
My second question is, I have in the Balance Sheet and P&L main groups (Group) and I would like to deepen them with subgroups (Subhoofd). However when I do this the main groups which are filled with the measurement (Non-Current Assets/Current Assets/Sharholders Equity/Services Total and onwards) are also subdivided again into subgroups while these should only remain (sub)totals.
Can this be prevented?
The new test file: Testfile
Thanks again for all the help!
Kind regards,
Marcel
marsclone
Please post a new post for new questions and full description of your problem.