Pivot Table formula giving incorrect results

[some_evil]

Hi Guys, attached is a xlsx file with pipes in our government area, and I am trying to add a field that multiplies its unit rate by the length, which in turn gives us a total value for each pipe type. But when I manually calculate the values I am getting different results.

Current Sewer Main Types.xlsx

see formula im using:



see results i get vs excel calculates:



Is there a better way for me to be calculating these total values so they are correct?

Thanks heaps.

[NBVC]

Try instead to add a new column to the main table called Total_Value with formula:

=[@Length]*[@UnitRate]

Then Add that column instead to the Pivot Table.

[some_evil]

Try instead to add a new column to the main table called Total_Value with formula:

=[@Length]*[@UnitRate]

Then Add that column instead to the Pivot Table.

Hi NBVC,

That is working great as a temporary measure, but this original table that the pivot is querying is a live feed from an SQL Database... and something I read yesterday made complete sense (i did not even think of doing this) but I added a new SELECT line... see below. This way every time new data is read into the table it is always current and up to date!

SELECT 
   tblAsset.AssetID AS [AssetID],
   tblAsset.AssetDescription AS [AssetDesc],
   tblasset.dimension1 AS [Length],
   tblTypes.TypeID AS [TypeID],
   tblTypes.TypeCode AS [TypeCode],
   tblTypes.TypeDescription AS [AssetType],
   tblTypes.RepCost AS [UnitRate],
   tblAsset.dimension1 * tblTypes.RepCost AS [Total_Price]           <---- this was the winning line that helped me out!
FROM
   tblAsset Left JOIN 
   tblTypes ON tblAsset.TypeID = tblTypes.TypeID
WHERE
 tblTypes.TypeCode Like '05.05%' And
 tblAsset.ValuationAsset = 1

Thanks heaps for your help.

[Herbds7]

Excel 2010 PowerPivot, SQL Database
How I think you should have loaded the data.
http://www.mediafire.com/view/bbr3t8...02_20_14a.xlsx