Results 1 to 4 of 4

Thread: Pivot Table formula giving incorrect results

  1. #1

    Pivot Table formula giving incorrect results



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    Click image for larger version. 

Name:	Microsoft Excel - Current Sewer Main Types.xlsx_2014-02-20_14-46-29.jpg 
Views:	14 
Size:	64.9 KB 
ID:	2072


    see results i get vs excel calculates:
    Click image for larger version. 

Name:	example errors.jpg 
Views:	16 
Size:	93.8 KB 
ID:	2071


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

    Thanks heaps.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    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.


  3. #3
    Quote Originally Posted by NBVC View Post
    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!

    Code:
    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.

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •