Forum Discussion

smalltownbear's avatar
smalltownbear
Frequent Visitor
2 years ago
Solved

Calculating a Sum at a Particular Level in a Hierarchy

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-le...
Need Help
  • smalltownbear's avatar
    smalltownbear
    2 years ago

    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.

smalltownbear's avatar
smalltownbear
Frequent Visitor
2 years ago

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.