Pivot Table Question

%3CLINGO-SUB%20id%3D%22lingo-sub-1387389%22%20slang%3D%22en-US%22%3EPivot%20Table%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1387389%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20size%3D%223%22%3ECan%20a%20Sub%20Total%20in%20a%20Pivot%20Table%20be%20used%20in%20a%20calculation%3F%26nbsp%3B%20I%20have%20a%20Pivot%20Table%20that%20groups%20projects%20by%26nbsp%3BCategory%20(there%20are%204%20categories).%26nbsp%3B%20I%20am%20then%20summing%20the%20labor%20by%20craft%20(Electrician%20(EL)%2C%20Plumber%20(PL)%2C%20etc.)%20in%20the%20Sub%20Total.%26nbsp%3B%20I%20would%20like%20to%20use%20the%20Sub%20Total%20in%20a%20calculation%20(e.g.%2C%20(Sub%20Total%2F6.768%2FNo%20of%20EE)%20where%20No%20of%20EE%20is%20another%20field%20(value)%20in%20the%20Pivot%20Table.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3EExample%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20EL%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20PL%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3ECategory%201%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Project%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2050%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2075%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Project%202%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B65%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2025%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3ESub%20Total%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B115%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20100%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3ECalc.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B2.43%26nbsp%3B%26nbsp%3B%26nbsp%3B%202.46%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3ENo%20of%20EE%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%207%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%206%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1387389%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1388314%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347586%22%20target%3D%22_blank%22%3E%40Purfive%3C%2FA%3E%26nbsp%3BYou%20can%20try%20using%20%3DSUMIFS%20to%20sum%20all%20the%20values%20with%20a%20given%20criteria%20as%20required%20by%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1388471%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388471%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347586%22%20target%3D%22_blank%22%3E%40Purfive%3C%2FA%3E%26nbsp%3BI%20suggest%20you%20have%20a%20look%20at%20the%20GETPIVOTDATA%20function.%20It%20allows%20you%20to%20pick-up%20values%20from%20within%20a%20pivot%20table.%20You%20could%20set-up%20some%20criteria%20outside%20the%20PT%2C%20dynamically%20get%20the%20data%20from%20the%20PT%20and%20then%20perform%20your%20calculations.%20See%20picture%20attached%20for%20an%20example.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-05-14%20at%2009.13.24.png%22%20style%3D%22width%3A%20555px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F191587i036A34C0BF5C6C62%2Fimage-dimensions%2F555x254%3Fv%3D1.0%22%20width%3D%22555%22%20height%3D%22254%22%20title%3D%22Screenshot%202020-05-14%20at%2009.13.24.png%22%20alt%3D%22Screenshot%202020-05-14%20at%2009.13.24.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Visitor

Can a Sub Total in a Pivot Table be used in a calculation?  I have a Pivot Table that groups projects by Category (there are 4 categories).  I am then summing the labor by craft (Electrician (EL), Plumber (PL), etc.) in the Sub Total.  I would like to use the Sub Total in a calculation (e.g., (Sub Total/6.768/No of EE) where No of EE is another field (value) in the Pivot Table.

 

Example

                         EL       PL                     

Category 1

      Project 1     50       75

      Project 2     65       25

Sub Total        115     100

Calc.              2.43    2.46

 

No of EE             7         6

2 Replies
Highlighted

@Purfive You can try using =SUMIFS to sum all the values with a given criteria as required by you.

Highlighted

@Purfive I suggest you have a look at the GETPIVOTDATA function. It allows you to pick-up values from within a pivot table. You could set-up some criteria outside the PT, dynamically get the data from the PT and then perform your calculations. See picture attached for an example.

Screenshot 2020-05-14 at 09.13.24.png