Excel Pivot Table Calculated Field formula to be used like sumproduct

Question

I have source data, as displayed in the image. Columns A through D

I want to create a pivot table using the source data without adding any new columns. My ultimate objective is to have the Pivot Table show totals first by country and then by product ( as shown manually green section)

I tried various different approaches, and the result is a pink colour pivot table that displays an inaccurate value. I added calculated fields to my results, but the results are now showing inaccurate numbers due to calculated fields.

Can somebody assist me in getting the right numbers into the pivot table? The pivot table in green was made by hand. (Predicted Results)

Answer 1

With a calculated field, you might not be able to achieve that since it will use the product of the sums rather than the sums of the products. For instance, you anticipate the following for Egypt|Prod 1:

(2,325*86)+(4,245*78)=199,950+331,110=531,060

But rather the Calculated Field is performing:

(2,325+4,245)*(86+78)=6,570*164=1,077,480

I would add your table to the Data Model and then use a Measure:

=SUMX(MyTable,MyTable[Quantity]*MyTable[Unit Price])

Changing the table name as required.