6 Replies Latest reply on Feb 7, 2019 12:50 AM by alex ra

# Comparing calculated field averages to individual values, or groups of individual values

Hi all,

I have a dataset where I created several calculated fields. I now need to compare the individual values in my dataset to the overall average. I want to have a plot where I have my global average, and filtered/selected individual values on the same plot.

In the attached doc, you can see in sheet1 I have the global averages, and in sheet 2 I have the same calculated fields, but filtered, and colored by "hosue"

1.How can I have both the global and individual values on the same graph?

2. How can I filter out houses 1 through 10, have an average line for those, and compare that average line to the global average?

3. How can I filter out houses 1 through 10, have an average line for those, and compare that average line to any individual values?

In excel, I would calculate the average for whichever group I want, and plot that next to individual values. I could compare any group of data to any other group, or any individual value to any group, etc.

• ###### 1. Re: Comparing calculated field averages to individual values, or groups of individual values

Hi Joe,

You you use LODs to control the level of aggregation:

Overview: Level of Detail Expressions - Tableau

Some examples here:

Top 15 Tableau LOD Expressions (Practical Examples)

With your example, to work out the global average I created this LOD:

Then I created a dual axis to add in the second metric:

Please see the attached  workbook with the above example.

Thanks,

Mavis

• ###### 2. Re: Comparing calculated field averages to individual values, or groups of individual values

Thank you for the answer, however, this does not seem to work with calculated fields, which is what I need.

• ###### 3. Re: Comparing calculated field averages to individual values, or groups of individual values

anyone?

How to LOD with calculated fields?

• ###### 4. Re: Comparing calculated field averages to individual values, or groups of individual values

Is it perhaps possible to export the calculated field values to a table that is tied to "days", so that I can plot it as a separate entity on a dual axis? This way my "days" tick marks will match, and I can use filters for comparing groups?

The other workaround I can think of is to manually do the calculation in Excel/ERP for the calculated field groups that I want, and have that as a separate, dedicated column that I use as a benchmark for my tableau filters.

Any help is appreciated

• ###### 5. Re: Comparing calculated field averages to individual values, or groups of individual values

Hi Alex,

The LOD is with calculated fields, are you referring to table calculations? Because I don't think there's a way to do this as table calculations is one of the the last operation in tableau's order of operations. If you can get the values in the data source then that's probably the best best way.

Thanks,

Mavis

• ###### 6. Re: Comparing calculated field averages to individual values, or groups of individual values

Thank you Mavis,

First, what I want is below

If I understand you correctly, you are saying that I cant make adhoc comparisons, much like you did with the LOD function and average weight, with table calculations (weight/day, or runsum fert/m22)?

1. Is this true?

If yes, then you are saying that in order to compare table calculation averages for a group, with individual values in that particular group, or any other adhoc group/individual values, that I need to make the calculation at the data level, and bring those into tableau for analysis. I would like to note, that this defeats the purpose of adhoc analysis in tableau, since I have to switch programs, and make a by-hand calculation for a specific group, re import the data, and make a plot. IF, for whatever reason, I did not forsee a particular table calculation in advance, then I would have to repeat the above-mentioned process for a new table calculation, and re-import my data AGAIN. Seems silly that I cant do this from within tableau.

2. If this is not true, then please assist.

3. Is there a workaround?

I can settle for exporting the values of my table calculations, and plotting those agains my adhoc group selections using filters. Is there a way to do this?

4. Am I making any sense? Is this a limitation of the software?

Alex