The challenge here is to filter data to one child, but at the same time show average calculated from all children.
These are contradictory requirements, but there is a workaround using parameters. It is not ideal and may not work well if the list of students is constantly changing, but it is an option.
The steps are:
- Create parameter that will select a child. No need to enter each name - just select 'List' and 'Add from field' button (see 'Selected Child' in the attached example). Note that this will not be automatically updated if the data changes. Show parameter control (right-click).
- Create a calculated field that will determine for each child if he/she was selected via parameter ('Is selected child' in the example).
- Create a calculated field using table calculation formula to calculate average for all ('Listening Avg' in the example). This will later need to be properly partitioned using 'Advanced' option.
- Build a view like shown in the example ('Listening fix stage'), putting 'Listening Avg' on dual axis and partitioning as shown (to see, right-click -> 'Edit Table Calculation...' )
- Right-click - Hide 'False' section of the view to only leave selected student visible, hide headers for 'Is selected child' and 'Student' (right-click -> untick 'Show Header')
- Add additional tweaks, like fixing the axis scale, adding title with parameter to show student's name, etc.
This may not be a really simple solution, but I can't think of a simpler one.
Book to send DB edit.twbx.zip 92.3 KB
Thank you for your reply, however I still seem to be having the same problem. I need the average to not change, and only the Student line to change if the name is changed. Even in your one when I select the Student name, both the Student and the Average line changes. Maybe I am still doing something wrong.
I suspect that what you see is an illusion of changing avg line, because the graph automatically adjusts the scale of the axis. Edit axis and fix the scale to between 0 and 7 and try again - you'll see that the avg line remains fixed.