Calculated Field or to add another column

Not sure what you want. Can show the sample.

Product Revenue
AAA 12
BBB 11
AAA 19
BBB 20
AAA 122
AAA 45
CCC 66
CCC 76
AAA 92

I have set a pivot table for this so that I have data field for sum of revenue for BBB and CCC. In addition to the current table, If I want to see the sum of AAA's revenue only , do I have to create another column called Product2 or can I use the calculated field?

Click on the button on the row field and select AAA

Why don't you clear all product and click Product AAA from the dropdown of the product
in the pivot table.This will only show you the total revenue for Product AAA that you want.

Another way is to maintain the current pivot table and create a new for the product of AAA.

Thank you.

Yes, I want to see sum revenue of AAA. But I would like to know if I could see both sum revenue of AAA and sum revenue of BBB and CCC combined on the data section of pivot table.

The original data set has only one column of "PRODUCT". It's my challenge to see two revenue types (one with AAA only and other with BBB anc CCC) at the same time on one pivot table.

Thank you PKTEE.

I could fix the original data as you stated. Just wanted to know if there is another way of getting the revenue without fixing the org. data.

I used the calculated field to see if this was possible,

Calculated Field1

=IF('Product'= "AAA",Revenue, "")

I guess I must fix the org. data to do this? Thank you all for helping me out!!

Hi,

As far I know , you can't do that without fix the orginal data.
Maybe it's possible using VB

If I found a solution will let you know.

Anyone out there got any idea ?

Hi,

I'm not sure what is wanted - however I suspect it can be done without adding an additional helper field to the source data.

It would help to have an image of the result.

I think just a grouped result is not wanted. So, that "AAA" would be one group of results and "BBB and CCC" grouped for the other data.

It sounds instead that an extra data field is wanted. One way to do this without a helper field is using a little SQL to do the work. Such as is available using Excel's 'external data' functionality. Which despite the name can be totally within the one file. Either via menu data, import external data, new database query OR by taking the external data option at the first step of the pivot table wizard. (If you search there will be info on using this online and certainly I have posted on it many time on the forum.) Within MS Query edit the SQL string along the lines below - very similar to your post. The first term is returning the revenue only for AAA and the second term the normal revenue: so they will both be in the pivot table. HTH. Regards, Fazza