Custom Subtotal in matrix (f.ex. EBITDA) based on parent/child hierarchy

[sylwestercz]

Dear DAX Experts! 

This is tricky case therefore your expertise & help is needed.

There is a P&L built based on excellent parent/child hierarchy as explained by Marco and Alberto (link to SQLBI: Parent-child hierarchies  ). Within the comments section there is a question from 2 years ago written by chitemerere which exactly explains my problem :  

"Excellent staff for presenting financial statements. However, the items in thefinancial statements need to be ordered in a certain way as shown below (images showing P&L). This needs to be added to the pattern if it is to be useful in practice, some sort order is required. This was achieved using a different method. In addition, there are other calculated items (Gross Profit, Operating Income, EBITDA, EBIT etc) which have to be added to the income statement, balance sheet etc and not part of Chart of Accounts/General Ledger." 

Alberto replied:  

"You are totally right, and this is the very reason why we did not call this pattern "balance sheet". It does not solve all the requirements of a financial statement, it is only a starting point. Besides, it does not have long legs, as soon as you introduce additional nodes that need to be computed (like the ones you cited) it becomes extremely more difficult.
We do have some POC of financial patterns, based on complex DAX and/or calculation groups. Unfortunately, none of these POC is clean enough to be presented as a pattern. The versions using DAX are full of SWITCH statements and the code is really complex. The version with calculation groups, albeit more elegant, suffers from performance issues and some other problems, both generated by the fact that calculation groups are very new and we are lacking experience.
For sure, we will publish more financial-targeted patterns as soon as we will be confident that they are a good and generic solution." 

If you're still reading, here is the point: I need to add custom subtotal (f.ex. EBITDA) but I am out of ideas and cannot find a way to write proper code with "full of SWITCH statements" which would work. 
If we look at original SQLBI example (pbix can be downloaded by the end of the article) - imagine that we have to create custom subtotal Adm & HC: 

• Adm & HC will contain all costs/accounts within Administration Expense and Human Capital together
• Adm & HC will not have corresponding accounts in fact table (Account Key) - it is only a custom subtotal
• Adm & HC will be a sum of 2 already exisiting measures : Total_Adm and Total_HC 
• I know it could be done through modification existing hierarchy and creating extra level but this has to be seperated

If you know how to solve it please suggest the code, perhaps it will point me into the right direction.

Thank you in advance.

Sly

[v-yiruan-msft]

Hi @sylwestercz ，

Are you trying to customize different total values on a visual with multiple hierarchies? In order to give you a suitable solution, could you please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

In addition, you can review the method in the following links. Hope they can help you resolve the problem.

Insert Custom Subtotals in the Matrix Visual

Use IsInScope to get the right hierarchy level in DAX

Clever Hierarchy Handling in DAX

Best Regards