Calculating a Sum at a Particular Level in a Hierarchy

[smalltownbear]

Suppose I have two tables (see image below), Agent and Transaction, such that an Agent can have 0-to-many Transactions. Also, each Agent can have up to one parent, potentially resulting in a multi-level hierarchy structure.

I'd like to display Agent premium amounts in a matrix visualization. In one column we would display the total premium for the individual agent and all of his/her children. In the second column we would display just the premium for the individual agent. 

I've figured out how calculate the first column by using the PATH function to calculate the hierarchy, creating individual calculated columns for each level of the hierarchy, and adding the hierarchy to the matrix along with the premium, but I can't figure out how to calculate the second column in the same matrix. I'm new to Power BI and am at a loss for how to go about this. Any help would be much appreciated. Thanks!

[Greg_Deckler]

@smalltownbear I would think it would be just a simple SUMX but for the exact solution I would need to know if there is a relationship between those two tables.

[smalltownbear]

Hi @Greg_Deckler . Thanks for the reply. 

There is a 0-to-many relationship between the tables: an agent can have zero or more transactions. 

I should probably give some further details. Following the advice given on the this article on the DAX Patterns website, I introduced a bunch of calculated columns to represent each level in the hierarchy. So my Agent table ended up looking more like this (see image below). 

I then created a hierarchy from the various Lvl columns and added the whole heirachy to the "Rows" property of the Matrix. I then added the Premium measure to the "Values" property of the matrix to get the "Total Premium" column from the image in by original post above. But I'm not sure how to go about calculating the "Individual Premium" column given everything I've done so far.

Have I taken the wrong path on this?

[Greg_Deckler]

@smalltownbear Can you post that data as text?

[smalltownbear · Accepted solution]

Actually I think I just figured it out. The following DAX seems to be working for me:

IndividualPremium = 
VAR currentAgent = 
IF(ISINSCOPE(Agent[Lvl4]),
    SELECTEDVALUE(Agent[Lvl4]),
IF(ISINSCOPE(Agent[Lvl3],
    SELECTEDVALUE(Agent[Lvl3]),
IF(ISINSCOPE(Agent[Lvl2],
    SELECTEDVALUE(Agent[Lvl2]),
IF(ISINSCOPE(Agent[Lvl1],
    SELECTEDVALUE(Agent[Lvl1])
))))

VAR individualPremium = CALCULATE(
            SUM(Transaction[Premium]),
            Transaction[Agent] = currentAgent)

RETURN
    IF(ISBLANK(individualPremium), 0.00, individualPremium)

NOTE: I probably should have used a SWITCH instead of nested IFs.