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

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?

  • 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.