# 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

• 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 Friday, July 26, 2013 3:30 PM
Friday, July 26, 2013 7:30 AM
• Marked as answer by Friday, July 26, 2013 5:06 PM
Friday, July 26, 2013 4:45 PM

### 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 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 Friday, July 26, 2013 5:06 PM
Friday, July 26, 2013 4:45 PM