1 Reply Latest reply on Mar 22, 2013 12:07 AM by Jim Wahl

# Countd on field1 if field2 = x

I am trying to create a calculated field for a set of data that has a many to many relationship between to fields. I've searched the forums and have found ideas that come close to what I'm trying to do but doesn't quite solve my problem. I need to find the distinct number of people that have and do not have a certain activity, then calculate a percentage based on these numbers. Listed below is an example of the data I am working with and what the report needs to look like. Any help would be greatly appreciated.

In this example I need to find the distinct number of persons that have activity a1, the number of a1 activities and the distinct number of p that do not have a1 activity. Then create a report that looks similar to the one below.

 person activity p1 a1 p1 a1 p1 a2 p1 a3 p2 a2 p2 a4 p2 a3 p3 a1 p4 a1 p5 a4

 A1 count 4.00 Distinct P 5.00 A1 rate 80.00% No A1 2.00

Thank you in advance for any insight into this type of calculation

• ###### 1. Re: Countd on field1 if field2 = x

Hi Tom,

If you can use an extract or database connection with COUNTD, this shouldn't be too difficult (see Accessing Count Distinct (COUNTD) | Tableau Software). I created the following calculated fields:

For A1 count: count of activities = COUNT(activity)

For distinct number of people with activity: [person count distinct] = COUNTD([person])   --- compute along activity

For total number of distinct people: [person count distinct total] = TOTAL(COUNTD([person]))    --- TOTAL removes the partition by activity

For number of people without activity = [person count distinct total] - [person count distinct]

For activity take rate: [person count distinct] / [person count distinct total]

See attached twbx.

Jim