Aug 05 2020 02:17 PM
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
Aug 06 2020 06:30 AM
Perhaps you may illustrate on the sample, it's not clear what is same ID with multiple ID and what exactly to calculate.
Aug 06 2020 09:43 AM
Let me know if you can see the image below. This is an example of what I need under Column E
Aug 07 2020 01:52 PM
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])))