Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sylwestercz
Helper I
Helper I

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

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 seperatedsylwestercz_0-1655140309008.png

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

1 REPLY 1
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.