Percent of Group Average Accross Periods

[#1]

Hi,

I am trying to compare individual performance to their group's average performance across periods.

I have the following formulas:

1. Metric: sum(Metric)

2. Group Average:

if(Metric > 0,

Sum({$<Individual= >} TOTAL <Group,Period> Metric)/

COUNT({$<Individual= >} TOTAL <Group,Period> Metric)

)

These work as intended in a straight table. I can either have both an individual and their group selected, or just the individual and the Group Average still works

Group	Period	Individual	Metric	Group Average
			131
b	0	n	68	67.5
b	1	n	63	61

However, when I change this table to a graph, if I select an individual and leave the group selection at the default, it shows the average across all groups (~36) instead of just that individual's group's average.

I have attached my document for reference.

My real data has about 5 groups, and the periods most often used will be weeks.

Thank you,

Elin

[ramoncova06]

so the expression in the set analysis is split in three parts

1. Group = P({<Individual={"$(=(GetFieldSelections(Individual)))"} >})
   • Your original problem was that you were excluding Individual from the expression and because you were not selecting any group QV was adding the total for both groups, with this we are telling QV to use the group that is associated with the Individual field
2. Individual =
   • this is to bring all Individu
     
3. Metric= {">$(=(0))"}
   1. since you were using an IF for Metric > 0, I used an advanced search  instead of the if, this performs faster than IF's