4 Replies Latest reply on Jul 28, 2015 12:09 PM by Alexander Mou

Distinct Count of Client by Product/Service

I need to be able to get a distinct count of clients that purchased Product A only, Product B only, and Both Product A & B.  It seems like a simple ask, but alas, the answer has alluded me.

I am attaching a sample data set with two products.  Any help would be appreciated.

Example:  Each line is an order.  Client A would be in the both category, Client B would be in the Product A category, and Client C would be in the Product B category.

My distinct count would then be:

Product A     1

Product B     1

Both A&B     1

Client A1
Client B1
Client C1
Client B1
Client A1
• 1. Re: Distinct Count of Client by Product/Service

Hello Marcia,

Assuming you data source only has these two categories (Product A and Product B), then we can use an LoD to create a new row level records for each product, at the client level.

Product A - Client =>

{FIXED [Client] : SUM([Product A])}

Product B - Client =>

{FIXED [Client] : SUM([Product B])}

Product Categories =>

IF [Product A - Client] >= 1 AND [Product B Client] >= 1 THEN 'Product A and B'

ELSEIF [Product A - Client] >= 1 THEN 'Product A'

ELSE 'Product B'

END

Notice that Product Categories is a Dimension, and not a measure. This is the glory of LoDs. They also us to compute row level results, across the addressed field. This relieves us having to do data densification and complex table calcs.

Finally, build out your Viz like so.

I added more rows in the Excel sheet I used, which is why it does not match your example, but the method is correct I promise.

If you have any difficulty with this, please let me know.

Regards,

Rody

1 of 1 people found this helpful
• 2. Re: Distinct Count of Client by Product/Service

This makes sense but...

I can't put the individual products into the formula.  They are just a dimension- and not a measure (I did try this with COUNTD instead of sum for this reason)- and they are inside the "products" category.

Since I can't make the first calculations I can't finish with the IF statement.

Does that make sense?

• 3. Re: Distinct Count of Client by Product/Service

Perhaps something like this would work.

Product A - Client =>

{FIXED [Client] : SUM([Product A])}

You could do

Product A - Client =>

{FIXED [Client] :  COUNTD(IF [Product] = 'A' THEN [Product] END) }

Then repeat for the other product(s)

Let me know if that doesn't work. I am not on V9.0 right now, but I can check this when I check your attached workbook when I get home tonight.

Regards,

Rody

• 4. Re: Distinct Count of Client by Product/Service

See if this is something you expected?

https://public.tableau.com/views/helpSampleData_AM/Sheet6?:embed=y&:display_count=yes&:showTabs=y

On Tue, Jul 28, 2015 at 11:20 AM, Rody Zakovich <