Help with pivot table calculated field

[MaffewW]

hi guys, bet this is dead easy, cant suss it out, first time worked with a pivot table, would apprecitiate your expertise


trying to add up some columns on a pivot table, though I want to perform the calculations within the table itself not outside. I want to total up some of the columns from the status field(PLL total) then work out that as a percentage as a sum of all columns. hopethat make sense, image attached. I've tried following the intructions I have though I'm not getting anywhere

cheers guys



Matt

Brighton, UK

 

[SkipVought]

Perhaps it would help to post some

sample SOURCE DATA,

explain how you are constructing the PT and

what you want to calculate, including

what you have tried that does not work.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[MaffewW]

sure, sorry, struggling with overheating laptop and wifi connection.

Pivot table has team leader in the page section to filter on.

In the rows it has field "agent" (each agent is categorised under each team leader). In columns I have a status field which summarised in the data section is a count of one of 11 values. What I want to do is add up 5 of these columns to make a new field called PLL. Then take the value of PLL as a percentage of the sum of all 11 columns.

What I have tried is clicking the pivot table toolbar, clicking formulas, then calculated fields. Thats where I'm stuck. The best I've managed is to get it to add another row though not sure where to go next!? Might not be explaining this the best, so apologies,I'm a DBA who every now and then gets some random other stuff put on me!!

Does the link I posted above help at all?


cheers Skip

Matt

Brighton, UK

 

[MaffewW]

here is some sample csv data (the second field is no relevant, I'm trying to total fom the values in the status field, exlc "Live", and "in progress")


TeamLeader,Parent Customer Ref(Main Q),status,agent
#N/A,CC00078065,Live,Soho
Gaz,CC00090686,In Progress,cpearson
Mike,CC00090829,Live,dyazdiantehrani
Mike,CC00091712,Live,asmith2
George,CC00094202,In Progress,cetherington
George,CC00094202,In Progress,cetherington
Mike,CC00097818,Live,ssmith1
George,CC00097819,Bad Data,cetherington
Jules,CC00097820,Live,paitken
Jules,CC00097822,Live,ldellow
Jules,CC00097825,Live,paitken
George,CC00097826,Live,cetherington
Stewart,CC00097827,Bad Data,dshumate
Jules,CC00097828,Live,paitken
George,CC00097829,Incompatible,ldougherty
Jules,CC00097830,Live,paitken
George,CC00097831,BT Winback,cetherington
George,CC00097835,Live,cetherington
Jules,CC00097836,Live,paitken
Stewart,CC00097837,Live,koshea
Jules,CC00097838,Buyers Remorse,paitken
Stewart,CC00097839,Bad Data,dsmith1
Stewart,CC00097841,Bad Data,dshumate
Jules,CC00097843,Bad Data,paitken
George,CC00097844,Live,jtulk
Jules,CC00097846,Live,dmorey
Stewart,CC00097847,Live,PBarrett
Jules,CC00097848,Live,dmorey
Stewart,CC00097849,Churn,dsmith1
Stewart,CC00097851,Live,PBarrett
Jules,CC00097855,Live,dmorey
Stewart,CC00097856,Incompatible,PBarrett
Jules,CC00097857,BT Winback,jlowell
George,CC00097859,Live,jkearns
Stewart,CC00097860,Live,PBarrett
Naomi,CC00097861,Live,jhopkins
Naomi,CC00097864,Live,jhopkins
Naomi,CC00097865,Buyers Remorse,lward
Naomi,CC00097866,BT Winback,npatel
Naomi,CC00097868,Incompatible,jhopkins
Naomi,CC00097870,Live,lward
George,CC00097871,Buyers Remorse,jfox
George,CC00097872,Live,jtulk
George,CC00097874,Live,jfox
Naomi,CC00097876,Live,npatel
Naomi,CC00097877,Live,lward
Mike,CC00097880,Live,jchurch
Naomi,CC00097882,Live,lward
Jules,CC00097883,WLR Failed,sbroomfield
George,CC00097888,Live,jtulk
Stewart,CC00097889,Live,pmccaffrey
George,CC00097891,Buyers Remorse,saston
Jules,CC00097892,Live,ldellow
George,CC00097896,Live,jtulk
George,CC00097897,In Progress,saston
Jules,CC00097898,In Progress,ldellow
Jules,CC00097898,In Progress,ldellow
Mike,CC00097899,Live,sbendiouis
Jules,CC00097900,Live,sdelamare
Academy,CC00097902,Live,klawson
Mike,CC00097903,Live,ssmith1
Mike,CC00097904,Incompatible,vharding
Academy,CC00097905,Live,klawson
Mike,CC00097907,Live,sbendiouis
Stewart,CC00097908,Bad Data,vfreshwater
George,CC00097910,Live,jtulk
Mike,CC00097913,Live,ssmith1
Stewart,CC00097914,Live,JToogood
Naomi,CC00097920,Live,npatel
Mike,CC00097923,Bad Data,vharding
Mike,CC00097923,Bad Data,vharding
Jules,CC00097925,Live,mfry
Mike,CC00097926,WLR Failed,sbendiouis
Jules,CC00097927,BT Winback,jlowell
Mike,CC00097928,Live,vharding
George,CC00097929,Live,jmacfadyen
Jules,CC00097931,Live,rhume
Mike,CC00097932,Bad Data,vharding
Naomi,CC00097934,Live,lward
George,CC00097935,Live,jmacfadyen
Jules,CC00097937,Live,rhume
Mike,CC00097938,Live,jnugent
George,CC00097942,WLR Failed,ldougherty
Stewart,CC00097945,In Progress,koshea
Stewart,CC00097946,Live,pveale
Mike,CC00097947,Live,vharding
Stewart,CC00097949,Live,koshea
Stewart,CC00097952,BT Winback,dsmith1
Stewart,CC00097954,Live,jtoogood
Stewart,CC00097957,Unknown,dsmith1
Mike,CC00097958,WLR Failed,jnugent
Stewart,CC00097959,WLR Failed,pveale
Naomi,CC00097961,Incompatible,smacfarlane
Naomi,CC00097962,BT Winback,ewilkinson
Naomi,CC00097963,Live,smacfarlane
Jules,CC00097966,Live,smew
George,CC00097967,Live,galleyne
Mike,CC00097968,Live,jnugent
Stewart,CC00097969,In Progress,wpryn1
George,CC00097970,Live,galleyne
Stewart,CC00097971,Live,dshumate
Naomi,CC00097972,Live,ewilkinson
George,CC00097976,Live,galleyne
Mike,CC00097978,Live,vharding
Naomi,CC00097980,Churn,ewilkinson
George,CC00097981,Live,galleyne
Stewart,CC00097982,Live,dshumate
George,CC00097984,Bad Data,galleyne
Naomi,CC00097985,Live,ewilkinson
Mike,CC00097986,Live,vharding
Naomi,CC00097987,Live,smacfarlane
Naomi,CC00097990,Bad Data,jchilds
Naomi,CC00097991,BT Winback,ewilkinson
Stewart,CC00097992,Live,dshumate
George,CC00097993,Live,galleyne
Mike,CC00097994,Live,jchurch
Naomi,CC00097995,Live,smacfarlane
Naomi,CC00097996,Live,ewilkinson
Naomi,CC00097998,In Progress,jhopkins
Mike,CC00097999,Live,ssmith1
Naomi,CC00098001,BT Winback,ewilkinson
Mike,CC00098002,Live,jchurch
Naomi,CC00098003,Bad Data,smacfarlane
Naomi,CC00098005,Churn,ewilkinson
Mike,CC00098007,Bad Data,jchurch
Naomi,CC00098009,BT Winback,smacfarlane
Stewart,CC00098010,Live,dmack
Stewart,CC00098012,Live,dsmith1
Mike,CC00098013,Live,jchurch
Naomi,CC00098014,Live,jgiddings
Naomi,CC00098015,BT Winback,smacfarlane
Naomi,CC00098017,Live,ewilkinson
Jules,CC00098021,WLR Failed,dmorey
Stewart,CC00098023,BT Winback,dsmith1
Stewart,CC00098028,Live,dshumate
George,CC00098029,Bad Data,jfox
Stewart,CC00098030,In Progress,dshumate
Stewart,CC00098030,In Progress,dshumate


Matt

Brighton, UK

 

[SkipVought]

The data you posted bears little resemblence to the pic -- ie no Sales Person/Date sold.

So what columns sum to PLL?

really, I don't see any way of donig this kind of a calc within the PT.

I might play around with a crosstab like this

transform Count(A.status)

SELECT A.TeamLeader
FROM `D:\My Documents\vba\crosssum`.`Sheet3$` A
WHERE (A.TeamLeader Is Not Null)

group by A.TeamLeader

Pivot A.status

and then use several adjacent columns to do my calculations and check the fill down formulas in columns adjacent to data in the Data range Properties window of Data/Get External data/New database Query - Excel files -- YOUR WORKBOOK -- YOUR SHEET containing the SOURCE DATA...

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}