Pivot Table Assistance - Cascading formula's within the same Employee ID with multiple Employee ID's

[TanyaRuyle]

We have a pivot table of multiple employee IDs that we are trying to calculate (cascading down) one amount  minus another amount (both amounts are in columns in the pivot table), but only want to cascade per employee ID

[#2]

@TanyaRuyle 

Perhaps you may illustrate on the sample, it's not clear what is same ID with multiple ID and what exactly to calculate.

[#3]

@Sergei Baklan 

Let me know if you can see the image below.  This is an example of what I need under Column E

[#4]

@TanyaRuyle 

Not sure I understood how you PivotTable was built. However, for such sample

add data to data model creating PivotTable and create a measure (in PowerPivot or by Add Measure interface) as

formula is

Calculation:=CALCULATE(AVERAGE(Table1[Payment]),FILTER(ALL(Table1[DCD]),Table1[DCD]<=MAX(Table1[DCD])))-
                        CALCULATE(SUM(Table1[Due]),FILTER(ALL(Table1[DCD]),Table1[DCD]<=MAX(Table1[DCD])))