7 Replies Latest reply on Sep 11, 2017 4:57 PM by Michael Ye

    Difference of CountD

    Jia Wu

      As the picture shows, I have a countd of the transactions by product. The number of transactions related to Product A + the number of transactions related to Product B is more than the Grand Total number of transactions, because some transactions sell both products. Is there a way to calculate countd (Product A) + countd (product B) - countd (all transaction), i.e. a countd of all transactions rated to both Product A and Product B?

       

      2017-08-28_17-44-17.jpg

        • 1. Re: Difference of CountD
          Hari Chandan Reddy Maryada

          Hi Jia,

           

          Could you please attach the sample workbook. Packaged workbooks: when, why, how

          2 of 2 people found this helpful
          • 2. Re: Difference of CountD
            Ivan Young

            Hi Jia,

            As Hari recommended it's always helpful to share a workbook or at least sample data.  That being said I think you can easily accomplish your goal with a LOD measure.

             

            Tran by Prod: {FIXED [Product Rollup] : COUNTD([Transactions]) }

             

            Use the sum of this new measure.

             

            Regards,

            Ivan

            • 3. Re: Difference of CountD
              Jia Wu

              Thank you for the answer. Actually, I had already tried fixed, but it did not work. To give a better example, please see the raw data below. I have 5 transactions, 2 of which sold both product A and B. The view I pasted in my original post is a countd of the transaction ID grouped by Product. So A would be 4, and B would be 3, and the grand total would be 5. What I really wanted to get is a countd of transactions that have both A and B. The answer here would be 2. What's the best way to do it in Tableau?

               

               

              Transaction IDProduct
              1A
              2A
              2B
              3A
              3B
              4A
              5B

               

                

              Product Countd
              A4
              B3
              Grand Total5
              • 4. Re: Difference of CountD
                Ivan Young

                Hi JIa,

                Thanks for the more detailed explanation. The easiest way I can think of is a custom grand total described here, Customizing Grand Total to Show Different Measure Than Table | Tableau Software .

                 

                My example requires creating 4 measures.

                 

                1. Distinct Products: COUNTD([Product])

                 

                2. Detail: COUNTD([Transaction ID])

                 

                3: Grand Total: IF [Distinct Products] = 2 THEN COUNTD([Product]) ELSE 0 END

                 

                4: Custom Measure:

                 

                IF Size() > 1

                THEN [Detail]

                ELSE [Grand Total]

                END

                 

                Then layout like below.

                 

                Regards,

                Ivan

                 

                • 5. Re: Difference of CountD
                  Zhouyi Zhang

                  Hi, Jia

                   

                  Here is another way by using LOD

                   

                   

                  Hope this could help

                   

                  ZZ

                  1 of 1 people found this helpful
                  • 6. Re: Difference of CountD
                    Michael Ye

                    Hi Zhouyi,

                     

                    Your calculation is instructive and meaningful. However, I think you might make a mistake. The calculation of Measure should be;

                     

                    IF COUNTD([Product])=1 THEN

                    SUM({ FIXED [Product]:COUNTD([Transaction ID])})

                    ELSE

                    COUNTD(IF { FIXED [Transaction ID]:COUNTD([Product])}=2 THEN [Transaction ID] END)

                    END

                     

                    Please advise.

                     

                    Thanks,

                     

                    Michael

                    4 of 4 people found this helpful
                    • 7. Re: Difference of CountD
                      Michael Ye

                      Hi Jia and Zhongyi,

                       

                      Moreover, here we know that we only have two products. However, if there are more than a thousand products, it's impossible to count them one by one. So more generally, we should use the following calculated field:

                       

                      IF COUNTD([Product])=1 THEN

                      SUM({ FIXED [Product]:COUNTD([Transaction ID])})

                      ELSE

                      COUNTD(IF { FIXED [Transaction ID]:COUNTD([Product])}={MAX({(COUNTD([Product]))})} THEN [Transaction ID] END)

                      END

                       

                      Thanks,

                       

                      Michael

                      3 of 3 people found this helpful