4 Replies Latest reply on Jun 7, 2018 2:02 PM by Jonathan Whiting

# Calculated Field that References a Particular Dimension Value

I'm dealing with a pretty large dataset (~3.2 million rows), so I'd like to do some calculations in Tableau. I've read some pages about level of details (like this), but these seem to deal with aggregation of data rather than referencing values for specific dimensions. I'll lay out the problem, and hopefully a guru can point me in the right direction.

The data looks something like this...

Model (dimension)Site (dimension)Flow (measure)Calculated Field

Model A

Site A15001500/1500 = 1
Model ASite B800800/1500 = 0.533
Model ASite C200200/1500 = 0.133
Model BSite A10001000/1000 = 1
Model BSite B800800/1000 = 0.8
Model BSite C700700/1000 = 0.7
Model CSite A500500/500 = 1
Model CSite B250250/500 = 0.5
Model CSite C100100/500 = 0.2

Really, there are two elements to this calculation:

1. Create a formula that references a fixed dimension (Site A) and
2. Segment the calculation to work within another dimension (model)

Any help would be greatly appreciated.

• ###### 1. Re: Calculated Field that References a Particular Dimension Value

I'm not sure I understand what you're trying to do exactly. Are you saying that the denominator of the calculated field is based on Model and the numerator is based on Site?

• ###### 2. Re: Calculated Field that References a Particular Dimension Value

Jonathan,

Assuming you have a single row for each  Model - Site

you can go with

flow / {FIXED [Model]: SUM( if [Site] ="Site A" then Flow end)}

the denominator means;

for every group of Model,  if the Site is A  return the Flow.

Now you have to aggregate this , and the aggregation choice depends on your dataset.

If you have a single row per Model - Site, you could use anything.

If you have multiple rows with site A, you may want to SUM the Flow.

And if you have multiple rows with site A, and the Flow is duplicated then you need  MIN,MAX or AVG.

Michel

• ###### 3. Re: Calculated Field that References a Particular Dimension Value

Ken, I bolded values to make things more clear. The denominator is always the value for Site A within the same Model.

• ###### 4. Re: Calculated Field that References a Particular Dimension Value

Thanks Michel, I didn't even think to add an if statement within the level of detail expression... very clever!

I have a single row per Model - Site, so I'll play around with an aggregator that works for the view.