Summing / Aggregating on Parent Child Hierarchies

[ap]

So I've been trying to figure this out using the 

Dax Parent-Child Patterns

However, I still can't get it to show what I want. 

I am looking to create a table that displays each Company and a roll up of the sales of their children. I think I am stuck at the Sales Amount Simple. I used the formula and it still only gives me the total at the node and doesn't iterate for the totals for the children. What am I doing wrong or is that doesn't work in Power BI?

[Sales] :=

IF (

    [BrowseDepth] > [MaxNodeDepth],

    BLANK (),

    SUM ( Transactions[Amount] )

)

[v-chuncz-msft · Accepted solution]

@ap,

You may refer to measure below.

Measure =
VAR c =
    SELECTEDVALUE ( Sales[Company] )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] ),
        ALLSELECTED ( Sales ),
        PATHCONTAINS ( Sales[Path], c )
    )

[jimsson07]

Hello, Thanks for this solution very useful.

Your solution work only if one element is selected throuht the slicer.

Do you have an idea , how can we do with more selections ? 

[ap]

Success! Thank you so much Sam!!!! You are the man. However, I have one thing to ask. We also split our account types. So I have a need to filter some of the accounts out. However, if I click on the visual filter to only look at Client A, your measure only counts the sales as the current node.

Measure =
VAR c = SELECTEDVALUE('Hierarchy'[Name ID])
RETURN
CALCULATE(
SUM(Sales[Sales]),
ALLSELECTED(Sales),
PATHCONTAINS('Hierarchy'[ID Path],c)
)

Even though there is an AllSELECTED...doesn't that ignore any filters applied?

[ap]

Figured it out, I used ALL instead of ALLSELECTED if I am filtering.  ALLSELECTED only removes implicit filters that are generated within the same query.

Measure =
VAR c = SELECTEDVALUE('Hierarchy'[Name ID])
RETURN
CALCULATE(
SUM(Sales[Sales]),
ALL(Sales),
PATHCONTAINS('Hierarchy'[ID Path],c)
)

[v-chuncz-msft]

@ap,

Glad to hear that. By the way, you may help accept solution. Your contribution is highly appreciated.

[HassanI]

I have an 8 level hierarchy, how do I do the opposite and show a NON-aggregated value in a clustered bar chart but still maintain a hierarchy.

In my data I have each person with their own sales value and this is related to a separate table which has the parent child relationship.

[ap]

Okay, so I also tried creating a new measure called Amount Including Children.

Amount including Children =
SUMX (
'Hierarchy',
CALCULATE(
SUM(Sales[Sales]),
FILTER(
ALL('Hierarchy'),
PATHCONTAINS('Hierarchy'[ID Path],'Hierarchy'[Name ID])
)
)
)

However, that didn't work either. For each row, I want to see if Name ID exists in the ID Name for the entire table not just at the current row, if it does count it into the Sales total...