Pivot: Summarize Services for Client and Net Profits Calculation

1. Pivot: Summarize Services for Client and Net Profits Calculation

EDIT:

Hi Belinda

Many thanks for getting back to me.

Alright, I have made some example of the end document how it shall look like.
Please find attached an excel with 3 sheets. One with the timesheet data, one with the fees that shall be billed and one with the Pivot table.

What is in column D of the Pivot table is what should effectively be in column C(if I made the settings in the pivot correctly).
If you could help with instructing about the correct setting directly with the Pivot it would of course be most helpful.

In addition I would like to make another column with the subtraction of column b - column c in order to get the net profit (within the Pivot table).

Is this something you might be able to support me with?

Speaking of - is there anyone who could move the post to the Pivot section?

2. Re: Summarize Services for a Client

why screen shot and not the file itself?

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

3. Re: Summarize Services for a Client

--> base post edited for overview reasons

4. Re: Pivot: Summarize Services for Client and Net Profits Calculation

Hi
Please see "Amended PVT" tab in the attached file.
L2 and down:
=IF(SUMPRODUCT(--(\$J\$2:J2&\$K\$2:K2=[@Client]&[@Services]))=1,VLOOKUP([@Services],'Services Overview'!A:B,2,0),"")

5. Re: Pivot: Summarize Services for Client and Net Profits Calculation

As for the calculated field - go to Pivotable tools---fields, items&sets----calculated field----
Put the name of the title you would like to have
in the formula section: = 'Original WIP Value EC'- Fee

6. Re: Pivot: Summarize Services for Client and Net Profits Calculation

Awesome, that is exactly what I was searching for!

One small thing what I do not understand how to look up - (at)Clients & (at)Services. Is there any chance to see what is behind those placeholders, respectively how can I move them to a different column e.g.?
I see that - if I delete the name after the (at) I can select any column. However, When I want to do the same on a different sheet I noticed that I first need to add some sort of function which I am not aware of

7. Re: Pivot: Summarize Services for Client and Net Profits Calculation

This is because you have a table formatting (which I presonally cannot stand )

if you convert the table to a range you can see the cell refernce instead of the header titles (table tools----convert to range)
If you would like to maintain the table formatting - you can change the titles and type manually the cell numbers (J2&K2 for example in L2, and J3&K3 etc, as you drag it down).

Hope this helps...

8. Re: Pivot: Summarize Services for Client and Net Profits Calculation

Wow - so easy =)
Where can I give tips? You've really helped me out, thanks a lot!

9. Re: Pivot: Summarize Services for Client and Net Profits Calculation

you're welcome

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1