20 Replies Latest reply: Apr 25, 2017 7:38 AM by Sandro Pividori RSS

    Pivot table numbers change when I drill down on rows

    Mike Grattan

      I have a pivot table which I would like users to drill down on rows as follows:

      Location

           Grower

                Ranch

                     Commodity

                          Work Order

       

      The number they will be viewing is a formula, based on other formulas. The resulting number for this formula seems to change as I drill down.  Here is the formula for Total Profit/Loss:

      Net Proceeds- ValueHarvested.

      Net Proceeds is defined as Sum(GrossProceeds) - Sum(GrossCost)

      ValueHarvested is defined as (Sum(Acres) * Avg(CostPerAcre))

       

      The final expression looks like this:

      sum(GrossProceeds) - sum(GrossCost)) - (Sum(Acres) * Avg(CostPerAcre)

       

      I've done some digging around in the forums and I can't quite figure out what might be causing the numbers to change as I drill down. Could it be that I need to use Aggr? Perhaps I need to use Dimensionality? Any help would be appreciated.  Thanks.

       

      Here's what the numbers look like before I drill down to level 3:

      Pivot numbers change 1.jpg

       

      When I drill down to level 3, it changes to this:

       

      Pivot numbers change 2.jpg

      Other numbers change too, not just the Total Profit/Loss number that I mentioned above.

       

      I created a separate "test" pivot table, that includes fewer measures. In the test pivot table, everything works fine:

       

      Pivot test numbers work.jpg

       

       

      I think it has something to do with the other measures in the "broken" pivot table; either there are too many (11), or one or more of them is causing my expression to get "confused".

       

      Any ideas or suggestions?

       

      Thanks!