Excel Pivot Table Calculated Field formula to be used like sumproduct

0 votes

enter image description here

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 by Kithuzzz
• 27,740 points
78 views

1 answer to this question.

0 votes

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 narikkadan
• 51,240 points

Related Questions In Others

0 votes
2 answers

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
274 views
0 votes
1 answer

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

answered Dec 11, 2022 in Others by narikkadan
• 51,240 points
37 views
0 votes
1 answer

Excel formula to calculate MIN in table filtered

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

answered 1 day ago in Others by narikkadan
• 51,240 points
18 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

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

answered Oct 7, 2022 in Others by narikkadan
• 51,240 points
255 views
0 votes
1 answer

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

It should look something like this: READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 51,240 points
75 views
0 votes
1 answer

Use Excel pivot table as data source for another Pivot Table

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

answered Nov 11, 2022 in Others by narikkadan
• 51,240 points
441 views
0 votes
1 answer

Excel Power Pivot/Data Model

Here is the answer for your question: ...READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 51,240 points
64 views
0 votes
1 answer
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 51,240 points
100 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP