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)

Jan 3 in Others 227 views

## 1 answer to this question.

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.

answered Jan 3 by
• 61,360 points

## How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

## Excel sum formula without table - Equivalent to the Math Sum Symbol

You can use SUMPRODUCT for this. Assuming X is in cell B1, ...READ MORE

## Excel formula to calculate MIN in table filtered

Try this: =SUBTOTAL(105;B2:B7) READ MORE

## What is the shortest formula in excel to covert text date format e.g. 17 Jan, 2023 into regular date format like 1/17/2023?

The SUBSTITUTE() method can be used to ...READ MORE

## Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

## Pivot Chart Table in Excel To Calculate the Count and Display the Chart

It should look something like this: READ MORE

## Use Excel pivot table as data source for another Pivot Table

Press the keys Alt+D+P in a new ...READ MORE