8 Replies Latest reply on Dec 20, 2018 7:41 PM by Amyl

    Sum for distinct ID

    Amyl

      Hi Tableau Users,

       

      I have a scenario,

       

       

      I would like to get sum for distinct product name on Grand Total.

       

      Example: Brand7 and Brand7A contains same product name PRD9002, on display i suppose to show as mentioned above but Grand Total instead of 40,523 i suppose to see 40523-3659 = 36864 on APR-18 Field and same for all months..

       

      Can some one please help me on this scenario?

       

       

      Thank you

        • 1. Re: Sum for distinct ID
          albert wong

          Hi Amyl,

           

          Can you just Right-Click Brand 7a on the table and select EXCLUDE or do you need to show the data?

           

          • 2. Re: Sum for distinct ID
            Amyl

            Hi

             

            Yes i need to show the data. and i can't exclude all duplicate id on the report. i think this is not right way to get the results.

             

            Shinichiro Murakami Can you please check this scenario.

             

            Thank you

            • 3. Re: Sum for distinct ID
              Joe Oppelt

              In some cases you can mess around with the grand total value.

               

              Consider this calc:

               

              IF FIRST() <> LAST() the SUM([Value])

              else

              [use a calc that computes what you want]

              END

               

              The values of FIRST() and LAST() are equal for the grand total row.  So for all other rows, display the normal SUM(), and for that last one, do something different.  (And then use that calc instead of SUM([Value]) that you're currently using.)

               

              But this doesn't always work -- especially if the field that is getting displayed in the data rows is an aggregate calc or a table calc.

               

              For kicks, create that calc above, but just display 9999 for now and see if you get 9999 in the grand total row.  If so, then we can figure out what calc will give you the distinct sum you are looking for.

              • 4. Re: Sum for distinct ID
                Amyl

                Hi Joe Oppelt

                 

                Thank you for your reply,

                 

                i am able to follow as you mentioned.

                 

                1) The sum is aggregated calculation.

                 

                2) i tried to keep 9999 value for your calculation but some how i didnt get grand total as you mentioned.

                 

                Can you please check once.

                • 5. Re: Sum for distinct ID
                  Joe Oppelt

                  For starters, I have the 999 working for you.  (I missed that you are doing grand totals both across and down.  I'm assuming you want the altered value to be the total running down.)

                   

                  So we have that, and it's going to work for you.

                   

                  See attached.

                   

                  Next, to figure out how to eliminate the dup Product rows, tell me about how/when this would occur.  Could PRD9002 also show up in a different [Industry Name1] ?  (We have only one on this sheet right now.)  And if it could show up under a different Industry, would it fall under the same [Brand1] names?  (So, for example, could it show up for Company1 AND Company2?  And if so, would it always be either Brand7 or Brand7A?)

                   

                  If it shows up under different companies, are you looking to eliminate only duplicates within each company?

                  • 6. Re: Sum for distinct ID
                    Amyl

                    Hi Joe Oppelt

                     

                    Thank you very much for your reply,

                     

                    Yes Product 9002  can show up in different industries, yes this product fall under the same brand Name.

                     

                    (So, for example, could it show up for Company1 AND Company2?  And if so, would it always be either Brand7 or Brand7A?)        ---- YES

                     

                    If it shows up under different companies, are you looking to eliminate only duplicates within each company?                   --YES


                    Thank you

                    • 7. Re: Sum for distinct ID
                      Joe Oppelt

                      Sheet 1 in the attached uses mu Calculation2 to shove 999 into the totals.  That's what I uploaded before.

                       

                      Sheet 1(2) -- I added SUM(Sales) to text so that you can see the sum you were getting before the 999s were added in.  So now in the grand total has 2 values in each cell:  My 999 for the "total" from calculation2, and 296,423 (in the first column) for the Tableau-generated total.

                       

                      Sheet 1(3) -- Now I'm using Calculation3.  it's the same as Calc2, except I have my own calc to get the correct total.  I now have 3 fields per mark:  Calc3 (which is the sum(sales) in the data rows), sum(sales) (which should match Calc3 in each data row cell), and [Skip Duplicates].  Take a look at that calc.  Actually, first look at [First occurrence of Duplicate] in the dimensions.  This calc identifies only one [Brand] per [Product].  (I think I could have left off the dimension for the month of Date.  But it doesn't change anything so I just left it in.)  I used MAX to pick one [Brand], but MIN would have worked just as well.  (It just would have identified BRAND7 instead of BRAND7A.)

                       

                      In the [Skip Duplicates] calc I tell Tableau to load the value only for the one identified [Brand].  Other rows will be empty.  And you can see that for PRD9002, I only get a value on BRAND7A.

                       

                      I use that calc to build a grand total.  You'll see in Calc3 I grab the SUM(skip Duplicates) instead of SUM(sales) when FIRST=LAST.  And you can see that in place of 999 now, I show 292,764.  Below it is the grand total of SUM(sales) (296,423).  The third value in the grand totals is the tableau-generated SUM(skip duplicates).  If you were to drag that off the TEXT shelf, my calc still results in the right amount.  And you get the correct display of SUM(sales) for both those data rows for Brand 7 and Brand 7A.

                      1 of 1 people found this helpful
                      • 8. Re: Sum for distinct ID
                        Amyl

                        Hi Joe,

                         

                        Thank you very much for your help, Thank you again for spending time for this.

                         

                        Happy holidays.

                         

                        Thank you