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

%3CLINGO-SUB%20id%3D%22lingo-sub-1569296%22%20slang%3D%22en-US%22%3EPivot%20Table%20Assistance%20-%20Cascading%20formula's%20within%20the%20same%20Employee%20ID%20with%20multiple%20Employee%20ID's%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569296%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20a%20pivot%20table%20of%20multiple%20employee%20IDs%20that%20we%20are%20trying%20to%20calculate%20(cascading%20down)%20one%20amount%26nbsp%3B%20minus%20another%20amount%20(both%20amounts%20are%20in%20columns%20in%20the%20pivot%20table)%2C%20but%20only%20want%20to%20cascade%20per%20employee%20ID%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1569296%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570615%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Assistance%20-%20Cascading%20formula's%20within%20the%20same%20Employee%20ID%20with%20multiple%20Employee%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750734%22%20target%3D%22_blank%22%3E%40TanyaRuyle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20illustrate%20on%20the%20sample%2C%20it's%20not%20clear%20what%20is%20same%20ID%20with%20multiple%20ID%20and%20what%20exactly%20to%20calculate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571066%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Assistance%20-%20Cascading%20formula's%20within%20the%20same%20Employee%20ID%20with%20multiple%20Employee%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571066%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20can%20see%20the%20image%20below.%26nbsp%3B%20This%20is%20an%20example%20of%20what%20I%20need%20under%20Column%20E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TanyaRuyle_0-1596732180164.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210661iDDACDD9D659F0DDC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TanyaRuyle_0-1596732180164.png%22%20alt%3D%22TanyaRuyle_0-1596732180164.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1573863%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Assistance%20-%20Cascading%20formula's%20within%20the%20same%20Employee%20ID%20with%20multiple%20Employee%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1573863%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750734%22%20target%3D%22_blank%22%3E%40TanyaRuyle%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20I%20understood%20how%20you%20PivotTable%20was%20built.%20However%2C%20for%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20804px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211031iD8560FB6FCC02759%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eadd%20data%20to%20data%20model%20creating%20PivotTable%20and%20create%20a%20measure%20(in%20PowerPivot%20or%20by%20Add%20Measure%20interface)%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20819px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211032iDDE9684200A9BB1D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3ECalculation%3A%3DCALCULATE(AVERAGE(Table1%5BPayment%5D)%2CFILTER(ALL(Table1%5BDCD%5D)%2CTable1%5BDCD%5D%26lt%3B%3DMAX(Table1%5BDCD%5D)))-%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20CALCULATE(SUM(Table1%5BDue%5D)%2CFILTER(ALL(Table1%5BDCD%5D)%2CTable1%5BDCD%5D%26lt%3B%3DMAX(Table1%5BDCD%5D)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

3 Replies

@TanyaRuyle 

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

Highlighted

@Sergei Baklan 

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

TanyaRuyle_0-1596732180164.png

 

Highlighted

@TanyaRuyle 

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

image.png

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

image.png

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])))