4 Replies Latest reply on Sep 7, 2018 9:34 AM by Simon Runc

    Tableau task help ( numbers don't match)

    Imran Azam

      HI Guys

       

      I am currently working on a task where i am transferring an excel pivot on to tableau and i need to display something like the below  ( numbers should match the below)

       

      Prod groupProd
      April 2018
      May 2018
      BestProject5418138

       

       

      The problem is I cant get the  numbers to match the above on tableau, when i include the opp id in the rows ( please see the attached)  it gets two numbers which add up to the above as soon as i remove this ( which i need to) i get completely different number.

       

      i have attached my workbook i am working with, i would be very grateful for any help.

       

      Thank you

        • 1. Re: Tableau task help ( numbers don't match)
          Simon Runc

          hi Imran,

           

          So the issue here is that your Calculation Level of Detail (requiring OppID to compute it correctly) and Display LoD is different.

           

          We can get round this by creating the AVGs you want at the level you want with a FIXED LoD

           

          [Sales per Month LoD]

          {FIXED [Pro Group],[Prod],[Opp  ID],(DATEPART('year', [Month])*100 + DATEPART('month', [Month])): AVG([sales per month])}

           

          btw  this part is just the MY(Month) in the filter (DATEPART('year', [Month])*100 + DATEPART('month', [Month]))

           

          This creates the AVGs you need 54 and 18,084 for each OppID, but as an LoD we can then SUM these when we remove the OppID from the Level of Detail

           

          Hope that helps, and makes sense.

          • 2. Re: Tableau task help ( numbers don't match)
            Ankit Patel

            Hi Imran,

             

            I am not sure if 18138 is correct for May. If you are looking for Average then by definition avg = total sum of all the numbers / number of items in the set

             

            In your case:

            Opp ID 21 -- Avg Sales for April is 54

            Opp ID 63 -- Avg Sales for April is 0 or null

            Final Output:

            54+0 = 54  &  54/1 = 54 which shows Correct output

             

            In your case:

            Opp ID 21 -- Avg Sales for May is 54

            Opp ID 63 -- Avg Sales for May is 18,084

            Final Output:

            54+18084 = 18138  &  18138/2 =  9069 which shows Correct output

             

             

             

            Can you check and verify the above?

            • 3. Re: Tableau task help ( numbers don't match)
              Imran Azam

              Hi Anik ,

               

              thank you for your help, but no this is not correct, i want to show, the final answer as 18138 

               

              can this be done?

              • 4. Re: Tableau task help ( numbers don't match)
                Simon Runc

                hi Imran,

                 

                Have a look at the solution i provided, it gets you to the 18,138