PowerPivotPro's FAQ Site

Scenario 1. There is organizational Employee table with employee and manager relation.

In order to get the name of employee’s manager or alias of skip manager, you need to define new calculated columns as follows
‘EMPLOYEES’[ManagerName] =CALCULATE(VALUES(‘EMPLOYEES’[Name]), ‘EMPLOYEE’[Alias]=EARLIER(‘EMPLOYEE’[ManagerAlias]), ALL(‘EMPLOYEES’))
‘EMPLOYEES’[SkipManagerAlias] =CALCULATE(VALUES(‘EMPLOYEES’[ManagerAlias]), ‘EMPLOYEE’[Alias]=EARLIER(‘EMPLOYEE’[ManagerAlias]), ALL(‘EMPLOYEES’))
The formulas above perform the functionality of LOOKUP function which was not implemented in PowerPivot v1. They get the value of ‘EMPLOYEES’[Name] or ‘EMPLOYEE’[ManagerAlias] for a given ‘EMPLOYEE’[Alias] which is the parent of current row, that is ‘EMPLOYEE’[Alias]=EARLIER(‘EMPLOYEE’[ManagerAlias]). We also need to set ALL(‘EMPLOYEES’) to consider entire ‘EMPLOYEES’ table, not the current row only.
Scenario 2. Sales table is related to Employee table that has parent child hierarchy.
EMPLOYEES
EmployeeID    Name   Alias  Email          ManagerAlias
453729        Artur  artsb  artsb@xternet.com  geora
453730        James  jamik  jamik@xternet.com  geora
453731        George geora  geora@xternet.com

 

In order to get the sales amount for each employee including the sales of his/her direct reports, you need to define the following calculations:

‘SALES’[Amt] = SUM(‘SALES’[Amount]) – Measure
‘EMPLOYEE’[Sales1Level]=[Amt] – Calculated Column
‘EMPLOYEE’[Sales2Level]=[Amt](‘EMPLOYEE’[Alias]=EARLIER(‘EMPLOYEE’[Alias]) || ‘EMPLOYEE’[ManagerAlias]=EARLIER(‘EMPLOYEE’[Alias]), ALL(‘EMPLOYEE’))

‘SALES’[Amt] is the amount of sales for given context, that is non filtered rows of ‘SALES’ table. ‘EMPLOYEE’[Sales1Level] is a calculated column that will calculate the sales amount for each employee. It will filter Sales table for each row of the table and evaluate the [Amt] based on that filter. ‘EMPLOYEE’[Sales2Level] is a calculated column that will calculate the sales amount for each employee and his direct reports. It will remove the default filter, defined by current row (see ALL(‘EMPLOYEE’)), but will apply different filter selecting the rows that have [Alias] or [ManagerAlias] equal to current ‘EMPLOYEE’[ALIAS].

Note: PowerPivot does not support Parent-Child Hierarchies (self related tables), and DAX does not support recursion, hence we cannot get the Sales Amount for arbitrary level, however we may define calculated columns and measures that will get the sales amount for any fixed level of hierarchy.