Pivot Table Calculations

[xxthegiantxx]

	A	B	C	D	E	F	G	H	I
	Material	Apr Volume	Price	April revenue	May Volume	Price	May Revenue	Volume Total	Total Revenue
1	X	10	1.00	10.00	5	1.00	5.00	15	15.00
2	Y	1	2.00	2.00	2	2.00	4.00	3	6.00

<tbody>
</tbody>

I am using a pivot table in which total monthly volumes are calculated. I am trying to get the pivot table to automatically calculate the total revenues (green letters) by multiplying the price times monthly volume. The price does not change. Therefore, i don't think i can have a row in the table with the price. I tried this using a vlookup formula to add price when a material is selected but the pivot table calculates the sum of these or avg. etc.
How do i
1. add a row in my pivot chart for the price
2. add a row that multiplies price time volume monthly to get me a grand total

 

[SpillerBD]

You can add a calculated field that performs that multiplication.
Caution, that may not provide a valid Column calculation, but will work for the row calculation.

 

[xxthegiantxx]

Spiller, can you give more detail on how to do that? From what i know, calculated fields only work if the item is a column in the pivot table. Price does not change. If i add it to the table as a column that uses vlookup to pull the price when a material in the column is detected, the pivot table will calculate the price as a sum, average, etc. This does not allow me to do an accurate calculation. In other words, using the table above, it should calculate B x C= D. Instead, C becomes a sum of all the data series with Material 'X'. Price does not remain unchanged.

 

[SpillerBD]

Spiller, can you give more detail on how to do that? From what i know, calculated fields only work if the item is a column in the pivot table. Price does not change. If i add it to the table as a column that uses vlookup to pull the price when a material in the column is detected, the pivot table will calculate the price as a sum, average, etc. This does not allow me to do an accurate calculation. In other words, using the table above, it should calculate B x C= D. Instead, C becomes a sum of all the data series with Material 'X'. Price does not remain unchanged.

It is not a concern for each row. At the Grand Total and Subtotal Rows it would be. There are DAX methods to get around those issues but I am only aware of and not knowledgeable in DAX.

The other route is to provide the calculation in the source data. For small data sets that's easy, but a bad habit when getting into large (ie millions of rows) data sets. But those data sets will force PowerPivot and DAX solutions anyway. (see Rob Collie, PowerPivotPro.com and Ken Puls https://www.powerquery.training/about-us/)