I'm trying to do a line graph in Qlik Sense looking at telephone sales agent performance that has 1 dimension (date) and three calculated measures based on sales (conversion (%), sales per hour (SPH), and total sales (£)). These measures are selectable in the y-axis, and show the relevant data on the line graph which is filtered in three ways - one for Campaign, one for Team and one for Agent.
So: Campaign > Team 1/Team 2/Team3/etc > Agents in each team
I would like to show a reference line for each of these three above measures that reflects the total average for the relevant metric.
So, if i pick agent John Smith, from Team 1 in Campaign 1, the graph will show (for example) the sales for that agent each day but the reference line will show the average sales across the board for the whole of Team 1 Campaign 1 so that it is possible to see how the agent is performing based on their contextual average. Change the agent selection and the reference line will stay the same if they are in the same team, but change anything else and it will recalculate the average.
So far the reference lines i'm making only reflect the average of the agent chosen by the filter, and i need them to base it on an unfiltered count. Is there anyway to create that average based on the total number of agents in the team or, if no team is selected, the total number of sales in the campaign? Any help appreciated.
