5 Replies Latest reply on May 4, 2017 10:29 AM by Elliott Stam

# how to solve average of averages in reference line

Hi Everyone!

attached is both my source data, a quick example in excel of my problem. and a workbook

as you can see by the reference line, tableau is averaging the average weekly scores.

this is skewing the overall average of the entire line

these represent QA scores

for any selected CSM Alias in the legend (there are 3 of them), i need tableau to show the average across all of the lowest level of scores

the difference to the managers who use this graph is between an excellent employee, and just a good one

can anyone help me?

• ###### 1. Re: how to solve average of averages in reference line

Hi Shen,

Happy to help, but I need some clarification. What exactly do you mean by "average across all of the lowest level of scores"? My interpretation of this is that you would want the average of of CSM Alias aare in this case, since it is the overall lowest in the chart. But if you click on aare in the color legend, that's exactly what you get - an average of aare's KANA Score (95.75%).

Are you wanting to always show aare's average in this chart (without clicking in the legend automatically see the reference line at 95.75% insteadof 97.19%)?

Or, perhaps you're trying to find the lowest score for each CSM Alias and average that result? If so, let me know if this is the direction you want to go...

If you can lay it out for me and give me a target to hit, I'm pretty sure I can make this work for you.

Elliott Stam - InterWorks

• ###### 2. Re: how to solve average of averages in reference line

Hi Elliot, I do not think I am being understood. First, Let's look at the data.

 TargetObsID CSM_Alias RevDate KANAScore 1306 crff 4/10/2017 94.44% 1308 crff 4/10/2017 94.12% 1343 aare 4/12/2017 94.44% 1383 crff 4/18/2017 100.00% 1389 aare 4/18/2017 100.00% 1390 aare 4/18/2017 94.12% 1398 chrdnh 4/18/2017 100.00% 1400 chrdnh 4/18/2017 94.74% 1416 chrdnh 4/11/2017 100.00%

here is the source.

Now, I'm visualizing the data week over week. So, some weeks there will be only 1 measure to show a mark, some weeks there will be several measures to show the mark. The measures are being aggregated by the Week. That's the problem.

When I select aare as my CSM Alias we have the bolded subset of data above.

in Excel I can demonstrate what I need to have happen...

 Avg. KANAScore Weekly Avg. KANAScore 100.00% 100.00% 94.74% 97.37% 100.00% 98.25% 98.69%

I need the reference line to show 98.25% which is the average against all of the measures across the line on the graph. Not what I am seeing, which is 98.69%, a skewed number, because there is a second aggregation (avg) happening with the two already aggregated weekly averages.

I hope that helps demonstrate what I need.

and I hope you can help.

S

• ###### 3. Re: how to solve average of averages in reference line

Hi Shen,

Based on what you said, my understanding is that you want this output:

Table for verification:

The formula needed was a level of detail calculation:

This formula calculates the average KANA Score for each CSM Alias and always outputs the same value regardless of how your Tableau view is aggregated.

Hope that helps! (Workbook attached for reference)

Elliott Stam - InterWorks

1 of 1 people found this helpful
• ###### 4. Re: how to solve average of averages in reference line

OMG Elliot, YES!!! thank you so much the syntax of this formula is really unfamiliar to me... is there a reference document / site which lists such formulae?

• ###### 5. Re: how to solve average of averages in reference line

That function is what's called a level of detail calculation, which can be pinned to one of three levels: 'FIXED', 'INCLUDE', and 'EXCLUDE'.  A fixed level of detail is what I tend to use the most, and it explicitly specifies the level of aggregation for your formula to be computed. The 'include' variety allows you to include extra dimensions which may or may not be in your visual (for example, if you were looking at a map of countries but your formula specified to include cities then metrics in that worksheet would be aggregated at the city level rather than country). The 'exclude' variety is the opposite of 'include', allowing you to specify certain dimensions to ignore.

Fixed is the most globally useful and applicable, in my opinion. It's great because regardless of how you construct your view, that number will always be the same. That becomes extraordinarily useful when building complex dashboards, or when you need a 'single truth' type of number which should stay the same no matter what.

Here's some info on calculations in Tableau, in general: Functions

Here's a useful and practical set of examples of level of detail calculations: Top 15 LOD Expressions | Tableau Software

Elliott Stam - InterWorks