Hi David,

When you insert a calculated field the calculation is operating on the aggregated values for the field. You can see this in the case of product PT_PN-UH861 PPT where there is a total quantity of 66 representing 3 rows of data, and the sum of the SELL. Unit Price for those 3 rows is 4999.02 and you'll notice the Sales Check value is 329,935.32 i.e. 66 x 4999.02 as opposed to what you're expecting of 109,978.44.

For this same reason, you don't get sub-totals and grand totals for these type of calculated fields because it wouldn't make sense to multiply the total unit price by the total units.

If you want to perform calculations like this in a PivotTable you need to use Power Pivot.

Mynda

Thanks  Mynda pointing out  the problem and methodology of the calculating method in the PT.

But, please help further demonstrate using Power Query , then, Pivot table to overcome this problem, thanks !

Hi David,

It doesn't require Power Query. Just Power Pivot, but Power Pivot is not available in all versions of Excel. Please check this list and ensure you have Power Pivot before I spend time providing a solution you can't use.

Mynda

I got Power Pivot as an add on in my Excel 2010, but not so sure how to work out a solution to solve the calculation problem with this PT.

I transform the data sheet to Table , but still not sure how to use the Power Pivot to  to overcome the  calculated field errors in the sample.  

[ ie Shipped Qty* Selling Price]

Hi David,

You need to write a DAX measure using SUMX to calculate the unit price x qty shipped e.g. in the attached file:

Check Total =SUMX(Table1, [SELL. Unit Price] * [SM Shipped QTY])

If you'd like to learn Power Pivot please consider my Power Pivot course.

Mynda

Thanks, Mynda, that's the solution we want, we can tell and enjoy the power of Power Pivot and Query.

It saves our effort to juggle around the complex data sometimes we are encountering.

Definitely,  I will consider to join the course suggested, but seems  a bit expensive for me, will seek my Company financial support.