3 Replies Latest reply on Mar 9, 2017 6:16 AM by Dave Seburn

    pivoted data does not sum up correctly

    Dave Seburn

      Hello.

       

      When I pivot my data to allow for aggregation, my data totals are not correct.  They are about 9X greater than what they should be.

       

      Data initially looks like this:

      initial data.jpg

      Output from this data before data pivot gives me totals of about 2.2 million:

      pre pivot totals.jpg

       

      Then I pivot the module manufacturers and I get totals that about 15 million:

      post pivot.jpg

       

      I need to pivot the manufactures because I need to show total based off manufacturer.  The problem is that the results are not correct.

       

      Any help is much appreciated.

       

      I have attached two workbooks: pre-pivot and post-pivot.

       

      Thank You!

        • 1. Re: pivoted data does not sum up correctly
          Shinichiro Murakami

          Hi Dave

           

          I'm afraid that your case is not a simple pivot.

          The data seems consist of "manufacturer" "model" and "quantity" for 8 or 9? different ID.

          (And model 1 seems like Application ID)

           

          It's quite difficult to handle these structured data set.

           

          Manufacturer 1/ mode 1 /qty 1 / manufacturer 2/ model 2/ qty2 .............

           

          Tableau need to have this structure

          manufacturer ID / Model /  Qty

          1  /  xxx / 3

          1 / yyy / 10

          2 / aab / 5

          2 / ddf / 12

           

           

          Your data is already pretty big and I don't want to try on my PC, but the opportunity is

          Create union and duplicate your data x 8 or 9?

           

          Then create three fields

           

          [Manufacturer]

          If [talbe name] = "sheet 1" then [Manufactuerer 1]

          elseif [talbe name] = "sheet 11" then [Manufactuerer 2]

          elseif [talbe name] = "sheet 12" then [Manufactuerer 3]

          .

          .

          .

          elseif [talbe name] = "sheet 17" then [Manufactuerer 8]

          end

          .

          [Model]

          If [talbe name] = "sheet 1" then [Application ID]

          elseif [talbe name] = "sheet 11" then [Model 2]

          elseif [talbe name] = "sheet 12" then [Model 3]

          .

          .

          .

          elseif [talbe name] = "sheet 17" then [Model 8]

          end

           

          [Qty]

          If [talbe name] = "sheet 1" then [Qty 1]

          elseif [talbe name] = "sheet 11" then [Qty 2]

          elseif [talbe name] = "sheet 12" then [Qty 3]

          .

          .

          .

          elseif [talbe name] = "sheet 17" then [Qty 8]

          end

           

           

          Then calculate..

           

           

          Thanks,

          Shin

          • 2. Re: pivoted data does not sum up correctly
            Jonathan Drummey

            Hi Dave,

             

            The problem is due to the pivot replicating rows and therefore replicating values. In the unpivoted set there are 94,195 records. In the pivoted set there are 659,365 records (7 * 94,195). Each raw value of the unpivoted fields is replicated 7 times because you pivoted 7 Model Manufacturer columns.

             

            This screenshot from the pivoted source adding Pivot Field Names to Columns demonstrates this, each column exactly replicates the results from the unpivoted source:

             

            Screen Shot 2017-03-09 at 1.27.57 AM.png

             

            In this view the Module Quantity jtd calc divides the Module Quantity measure by the count distinct of Pivot Field Names (which is always 7) and generates the correct results:

             

            Screen Shot 2017-03-09 at 1.29.35 AM.png

             

            Workbook is attached, let me know if you have any questions!

             

            Jonathan

            2 of 2 people found this helpful
            • 3. Re: pivoted data does not sum up correctly
              Dave Seburn

              Thank you!  Works perfectly.

               

              Best Regards, Dave