Log In
Register
Home
December 5, 2016
Offline
Can not understand why PT Table total not tally , pls refer attached
July 16, 2010
Offline
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
December 5, 2016
Offline
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 !
July 16, 2010
Offline
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
December 5, 2016
Offline
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.
December 5, 2016
Offline
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]
July 16, 2010
Offline
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
December 5, 2016
Offline
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.
1 Guest(s)
