Viewing two variables in one pivot table

[irampuria1]

Hi,

I am having some trouble with data in a pivot table. In the data source, each row is the name of a product, e.g. Product XYZ and each column heading is the month. The table I have charts the amount of units sold and the total revenue from those products (values are hard coded). I am trying to create a pivot table that will show the breakdown of units sold/month AND the revenues generated/month. This is a little problematic, as the pivot table is unable to distinguish between the "units sold" cells and the "revenue" cells, so uses both in the same table.

Is there any way for me to show the revenue and units sold separately (in one or more pivot tables), but from the same data source?

Thanks in advance,
IR

[wanmuhd]

I think it is possible.
would you want to share the data as it more easier to do it

[irampuria1]

Unfortunately I am not able to share the exact data as it is sensitive. First row has the month. Second row has "Quantity sold" measured per unit and "Revenue from Sale" measured in dollars (split up by month). Column A has the names of the product. I have attached a very basic example spreadsheet showing these headings. Thanks!

IR

[southward]

Hi IR,

The example you provided isn't very helpful.
In order to provide an effective solution, you need to provide a more detailed example of your "raw" data and an example of what you want/need in the finished product.
Protecting "sensitive" data is very understandable. Just change the names of the products to something like XYZ1, XYZ2, ets....

Looking forward to helping provide a solution.

Cheers

[irampuria1]

I have attached an example sheet. I have two or three year's worth of data in the same format on separate tabs.

I am trying to create a pivot table that will allow me to group these products into similar products and analyze sales and revenue trends over 3-4 years.

Ideally, I would have two slicers, one that splits "Amount" and "Qty" into two, and another for the year and month so I can analyze by each year individually or different months together et. This will allow me to see the revenue and unit amounts over time, split up by month. The issue I am having is setting up the pivot table itself, one that will allow me to see the "Qty" values and "Amount" values separately and together because the units are different...

I hope this makes sense!

Thanks,
IR

[wanmuhd]

By looking at your excel sheet, I couldn't imagine how to do the pivot table.
Nevertheless, if you can arrange your data as per Sheet 1 (as attached), it would more easier to do pivot table.
I tried to do mock-up of the pivot table for your consideration

[southward]

I agree with wanmuhd. The data format you provided can't be turned into a Pivot Table.

The data sheet you provided looks like a report format that was produced by another data system.
In order to produce effective and usable Pivot Tables you need the actual raw data that would look something like the example wanmuhd provided.
For the data you presented the raw data would consist of thousands of rows. Each row would be Product Name, Date, Qty and Amount.