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.
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
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.
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)
averageoftheaverage.twbx 42.3 KB
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?
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
Glad this helped you out!