Forum Discussion
Pivot Table Assistance - Cascading formula's within the same Employee ID with multiple Employee ID's
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 cas...
Perhaps you may illustrate on the sample, it's not clear what is same ID with multiple ID and what exactly to calculate.
Let me know if you can see the image below. This is an example of what I need under Column E
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])))