In order for Tableau to "only count each person once" on a set of transactional data like you have (record for each question for an ID)....you need to "Group" the data by ID. That is what you're doing in your step 2 Goal Weight. You're essentially saying: disregard region and question when computing the average. When you average each repeating value within an ID you get 1 instance of that value.
There are 2 different levels of aggregation needed for this type of question:
1) avg the records for each ID (A min or max would work here too since they're always the same value)
2) avg the results of step 1
You've already got 2.99 showing on page 2 by aggregating to only ID. What is it you're looking to achieve?
Do you need to present that 2.99 a different way or with different dimensions on the visual?
No dimensions on the visual?
Because of the requirement of 2 levels of aggregation, what you're looking for will most likely need to involve table calcs.
Thanks for your help! I see what you mean about grouping by ID. Are you writing the SQL script in a table calc? i.e. How can I achieve this?
To answer your question, no, I wasn't using SQL in a table calc.
I was only using the language SQL to describe what is happening when you use a table calc. The table calc I used in the included workbook should be sufficient.
The concept is that you average the average [weight] of ID's.
This is done with a calc like so: window_avg(avg([Weight]))
If you wrap a measure in avg it will take all of the instances of that measure within each instance of the lowest level of detail (ID) and average them. Meaning if you have 5 ID's you'll have 5 resulting averages.
The window_avg will take the 5 resulting averages and perform an average on them. The output happens at all instances of an ID which causes repeating values:
In your data you have 7 ID's. This means you have 7 averages. A window_sum on the those averages results in the number 2.99.
You only need to present this once so you can create a calc of: index() place that on your filters shelf and set to true.
This gives you one row with the value you need:
measure for all ID's. For presentation's sake, you only need one instance of 2.99 so you can use and index filter set to 1 and hide the header for ID