Parent-Child Hierarchy special calculated measures

[Anonymous]

I am using the parent-child hierarchy as outlined here: https://www.daxpatterns.com/parent-child-hierarchies/#  This does a great job of organizing the levels to expand only as far as needed for each Category and showing the appropriate amount for each level.

However, I need to adjust a few things. 

1. (Red) I need the % column to be calculating % based on the Total for Group A ("Total Base" for current line/"Total Base" for Group A). So for September, Group A line should show 100%, Equipment = 3.1% (449,382/14,529,256), Group B line = 33.6%, Equipment Rental = 0.7%, etc.
2. (Green) I need a line (Whether leveraging subtotal or having a level in the hierarchy) that will allow me to calculate "Total Base" as Group A - Group B. Currently it's shown as a subtotal which is just adding them together. Something I can do in the calculated columns/measures (below)?

I have the Entity table and Path/Level fields as the link above pointed out. I also have the fields on my transactional table:

Sum Amount = SUM ( TranTbl[AMOUNT] )

Total Base =

            VAR Val = [Sum Amount]

            VAR EntityShowRow = [EntityBrowseDepth] <= [EntityRowDepth]

            VAR Result = IF ( EntityShowRow, Val )

            RETURN Result

Is this possible?

[v-janeyg-msft]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description,II think I understand what you need,but it's not clear about the specific field type and data, it is difficult to reproduce the scene you want.

Could you please share some sample data with OneDrive for business or pictures?So we can help you soon.

Best Regards

Janey Guo

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

[ChrisMendoza]

@Anonymous - If you download the PBIX file from the blog post you'll find:

% Parent = 
VAR BrowseLevel = [AccountBrowseDepth]
VAR Numerator = [Total]
VAR Denominator = 
    SWITCH (
        BrowseLevel,
        1, CALCULATE( [Total], ALL ( Account[Level1] ) ),
        2, CALCULATE( [Total], ALL ( Account[Level2] ) ),
        3, CALCULATE( [Total], ALL ( Account[Level3] ) ),
        4, CALCULATE( [Total], ALL ( Account[Level4] ) ),
        5, CALCULATE( [Total], ALL ( Account[Level5] ) ),
        6, CALCULATE( [Total], ALL ( Account[Level6] ) ),
        7, CALCULATE( [Total], ALL ( Account[Level7] ) )
    )
VAR Result = DIVIDE ( Numerator, Denominator )
RETURN
    Result

I was going to say some sort of CALCULATE([MEASURE],ALLEXCEPT('TABLE',[COLUMN])) but since you're using sqlbi.com's pattern I figured there was some little trick 🤔

[Anonymous]

@ChrisMendoza  I don't actually want it to just show % of parent. I want it to show % of Group A. Basically what I'm doing is a P&L report. I want to show each line of revenue (Group A) and expenses (Group B) as a % of Revenue (Group A total being total Revenue).