DAX subtotals P&L reporting Direct Query

Question

Hello everyone,&nbsp;I am making a P&amp;L in Power BI with a direct query connection with SAP data and a hierarchy from SAP. The situation is that the SAP hierarchy could not be changed. I want to format the hierarchy with some small changes. How do i fix this at the best way? Is it possible with DAX?&nbsp;In the attachment i have provided a p&amp;L file and some example data.&nbsp;Revenue is a positive number and costs are negative. This two minus each other is the subtotal of the EBITDA.&nbsp;What i want to create is the following:GL_account_level 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;RevenueGl_account_level 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Revenue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Revenue carsGL_account_level 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CostsGl_Account_level 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cost&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Loan costsEBITDA (subtotal)GL_account_level 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DepreciationGl_account_level 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Depreciation&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Depreciation carsEBIT (subtotal)GL_account_level 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Interest(Regular exploitationresult subtotal)GL_account_level_3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Extraordinary income and chargeSubtotal result before tax&nbsp;The current situation is the following:EBIT (level 1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;EBITDA (level 2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Revenue (level 3)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Revenue cars (level 4)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Costs (level 3)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Loan costs (level 4)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Interest and depreciation (level 2)Extraordinary income and charge (level 1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Extraordinary income and charge (level 2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Extraordinary income and charge (level 3)Total Power BI result before taxI am searching very long for a good way how to fix this, so if anyone can help me i'm very happy:)

anonymous · Answer

https://we.tl/t-UYtrNxpLlC&nbsp;with a wetransfer link to the files

v-yalanwu-msft · Answer

Hi， Anonymous&nbsp;；
Is this the result you want to present? I don't quite understand the Gl_Account_level 4 in your description.

&nbsp;
Best Regards,Community Support Team _ Yalan WuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

DAX subtotals P&L reporting Direct Query

2 Replies

Related content

P&L - Paths

Subtotals within P&L Structure

rsQueryMemoryLimitExceeded Error in P&L / Balance Sheet Measure (Small Dataset)

Subtotal within P&L

P&L variances (formatting % and absolute values)