• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to calculate a item in the pivot table

M

max4asd

Member
Hi, everybody i would like to calculate the item field in a pivot table. example is shown under.


Salesmen Month year Sales

Allan Feb 2011 1,500.00

Allan Feb 2012 1,600.00

Mullen Feb 2011 2,000.00

Mullen Feb 2012 1,900.00

Dullen Feb 2011 1,200.00

Dullen Feb 2012 1,950.00


If we build a pivot table and want to calculate the performance of each salesmen in the pivot table itself how can i calculate the performance.


Thanks
 
Hi max4asd,


I tried with your data and found that i would be easier if we work-around it see this that does not use a pivot table either, just a simple sumproduct() formula. Note how data is entered.


http://dl.dropbox.com/u/60644346/Sales_Target.xlsx


Regards,

Faseeh
 
Thanks for your input but in real we will have tens and thousands of records and we like to summarize in then we need pivot table then how it can be done in the pivot table that was my question
 
Hi max4asd,


I have searched but unfortunately I could not reach one, there is an option in pivot tables where we can enter formula and calculate '% of diff' (using either preset formula or entering our own formula) but that works great till we are in 'Values' but once we reach right most column of 'Grand Total' things start getting problematic.


Thanks,

Faseeh
 
Hi max4asd,


I think you have got it! See the reworked file:

http://dl.dropbox.com/u/60644346/Sales_Target_Reworked.xlsx


Regards,

Faseeh
 
Thanks faseeh but my question was how can we calculate to compare 2012 vs 2011 in the example that you have posted in excel
 
Hi max4asd,


Here is the the reworked file comparing 2011 Vs 2012:

http://dl.dropbox.com/u/60644346/Sales_Target_Reworked_2.xlsx


This picture shows how you can do this:

http://dl.dropbox.com/u/60644346/Sales_Target_Pic.bmp


Regards,

Faseeh
 
Hi Faseeh,


Thanks for your feedback but here i am not asking for how to compare the performance of sales Vs. target.In the attachde file your comparing sales vs. target achived What i need to know how to calculate the performance of sales of 2012 Vs. 2011.


I hope you understand exactly what i am asking for? Sales comparison of 2012 Vs. 2011
 
Hi max,


Can you show me how the final table will look like and the formula you will use to calculate the performance. Commonly people use a 'target' as set criteria for sales. Lets say if a sales person was set with target of 1000 and he came up with sale of 900 he achieved 90% of the sales target.


Can you upload a sample file with data the final output result you are seeking. This will help solve the problem.


Faseeh
 
Hi Max ,


Have your pivot table as follows :


1. Month under Column Labels , Salesmen under Row Labels and Sum of Sales under Values


2. Place the cursor in the Row Labels row , and click on Options -> Calculated Item


3. For Field Name , enter 2012_vs_2011 , and in the formula box , double click on Feb,2012 from the Items list ; it will be inserted in the formula box ; enter - (minus) and double click on Feb,2011 ; this will also be inserted in the formula box.


4. Click on Add and OK.


5. Repeat the above steps to get the percentage values ; in this case use the item 2012_vs_2011 and Feb,2011 or Feb,2012 depending on how you want to see the difference.


Narayan
 
Thanks Mr. narayan,


I did try it but its not working its giving me a message "Pivot table formulas can only refer to items in the same field as the calculated item" coulds you please post an example of what you are trying to explain it will be great.


I appriciate and thank you for your effort.
 
Hi Max ,


Check at the following link :


https://docs.google.com/spreadsheet/ccc?key=0AkKMpuzr3MTVdDZhTFRoS0VPc0RiczJrVDRpT1RvQnc


Narayan
 
Faseeh,


Here in sales we do both comparison how you are performing vs. target and how you are performing vs last year for the same month.


For example my sales last year i.e. 2011 feb is 900 and my target for 2012 feb is 1000 and and sold 800 so i achieved target 80% and vs. last year i am 88.8% which means i did not achieve target and same time i am below last year sales.


Hope you get what i am looking for?


Thanks
 
Hi Max ,


Can you check out this link :


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21174


Narayan
 
Thank you very much Mr. Narayan but if i have 12 consecutive months i have to calculate one formula each for every month and then i can see the perc_Diff.


Is they any other smart solution to do this?
 
Hi Max ,


If you have 12 comparisons to be made , I think 12 columns will be required ! You can combine both the difference and the % formulae in one , but it cannot get any smarter than that.


Possibly others can suggest something better.


Narayan
 
Hi max,


Narayan's solution is the best possible one at the point. I have worked out an alternate based on pivot table but not actually pivot table. Might you like it, have a look at this:


http://dl.dropbox.com/u/60644346/Sales_Target_CustomTable.xlsx


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top