Calculated Dimension

[firmographs]

I'm trying to configure a relatively simple calculated dimension.  

We have data for organizations, contacts, and email addresses.

As a calculated dimension, we want the number of email addresses per organization.  

We are trying to create it this way:

=aggr(count(distinct Email),[Organization])

Then in a table, we want to use the calculated dimension along with the measure, which would be count([Organization]).

We aren't quite getting the results we want yet.  The count([Organization]) measure is a little off compared to a hand count.  Any ideas on how we need to tweak the calculated dimension to yield the result we want?

[JordyWegman]

Hi,

Try this:

Dimension:
Organization
Measure:
Count(Distinct Email)

You don't need the aggr for this. Now you will get all the unique emails per organization.

Jordy

Climber 

[firmographs]

That is the measure.  We are looking to create a dimension that is the count.  This way, we can use the count as a "bucket", and count the number of times that organizations on our list have 0, 1, 2, emails.  

For example, with 10 organizations:

0 email = 2 orgs

1 email = 4 orgs

2 email = 3 orgs

3 email = 1 orgs

4 or more email = 0 orgs

[JordyWegman]

You can't use the outcome of a measure in a calculated dimension.

Count(Category) doesn't work

IF(Count(Category) = 2,1,0) will work

I think you need to prepare this in your script and then you can select on it.

Jordy

Climber

[tresesco · Accepted solution]

Your expression should be:            count(Distinct [Organization])