none
DAX - Best way to perform custom aggregations in parent/child hierarchies?

    Question

  • I've been stuck on a problem for longer than I'd like in developing a DAX measure in SSDT.

    I implemented a parent/child hierarchy for my Employee table similar to the method described here: <removed because I'm still unable to post link until 'verified' - Alberto Ferrari's blog>

    I also looked at unary operators here, but did not achieve the result I was looking for: <removed because I'm still unable to post link until 'verified' - Alberto Ferrari's blog>

    My problem is essentially this: I have an Employee table with several levels of salespeople (John reports to Mike, who reports to Sue). They each have a Quota, which is stored in a FactQuota table along with a DateKey at the monthly level (ex: Quota for Sue for Jan-2012 = $100,000; Mike also has his own quota for Jan-2012 at 75,000, and John at $50,000). Assuming the quota remained the same for Feb and Mar, the Q1-2012 quota for Sue would be $300,000.

    When implementing these parent-child hierarchies, the aggregation of sales is performing exactly as I'd like: all of Sue's subordinates' sales roll up - so if John and Mike both have sales exceeding $100,000, she has met her quota. What I do not want is the Quota Amount aggregating by employee. What I see is a Quota total of $225,000 because it has aggregated all 3 employees quotas. It should sum Sue's Quota and ignore all child amounts.

    I've posted a quick mock-up here of what I am seeing:

    <removed because I'm still unable to post images until 'verified'>

    Can anyone steer me in the right direction? 

    Greatly appreciated!

    Thursday, July 25, 2013 10:52 AM

Answers

All replies

  • Hi,

    assuming you have a calculated column called [Level] which is defined using PATHLENGTH()-function you can use the following:

    CurrentEmployeeQuota:=CALCULATE(
    SUM('Sales Quota'[Sales Amount Quota]),
    FILTER('Employee',
        'Employee'[Level] < MAX('Employee'[Level])))
    hth,
    gerhard

    - www.pmOne.com -

    Thursday, July 25, 2013 6:28 PM
  • Hi,

    assuming you have a calculated column called [Level] which is defined using PATHLENGTH()-function you can use the following:

    CurrentEmployeeQuota:=CALCULATE(
    SUM('Sales Quota'[Sales Amount Quota]),
    FILTER('Employee',
        'Employee'[Level] < MAX('Employee'[Level])))
    hth,
    gerhard

    - www.pmOne.com -

    Unfortunately no luck with this. For Level, do you mean the depth of the hierarchy? For example, I'm passing in the ID|ID2|ID3|etc field into PATHLENGTH().
    Thursday, July 25, 2013 7:05 PM
  • [Level] is a calculated column defined as =PATHLENGTH(PATH([EmployeeKey], [ParentEmployeeKey]))


    - www.pmOne.com -

    Thursday, July 25, 2013 10:24 PM
  • some slight change to the formular:

    CurrentEmployeeQuota:=CALCULATE(
    SUM('Sales Quota'[Sales Amount Quota]),
    FILTER('Employee',
        'Employee'[Level] = MIN('Employee'[Level])))
    


    - www.pmOne.com -

    • Marked as answer by fblissjr Friday, July 26, 2013 3:30 PM
    Friday, July 26, 2013 7:30 AM
  • some slight change to the formular:

    CurrentEmployeeQuota:=CALCULATE(
    SUM('Sales Quota'[Sales Amount Quota]),
    FILTER('Employee',
        'Employee'[Level] = MIN('Employee'[Level])))


    - www.pmOne.com -

    This worked! Thank you!
    Friday, July 26, 2013 3:30 PM
    • Marked as answer by fblissjr Friday, July 26, 2013 5:06 PM
    Friday, July 26, 2013 4:45 PM