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

# Difference of CountD

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?

• ###### 1. Re: Difference of CountD

Hi Jia,

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

3 of 3 people found this helpful
• ###### 2. Re: Difference of CountD

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

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 ID Product 1 A 2 A 2 B 3 A 3 B 4 A 5 B

 Product Countd A 4 B 3 Grand Total 5
• ###### 4. Re: Difference of CountD

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

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

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

Thanks,

Michael

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

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