Help required with Expression

[awhitfield]

Can any one help with this one please?

In the source data there are lots of rows for Adverse effects, but when you group up by ccg there are 12 sums to be averaged:

CCG	programme_category	Value	Avg
00T	Adverse effects and posioning	30607	61579
00V	Adverse effects and posioning	80011	61579
00W	Adverse effects and posioning	2274	61579
00Y	Adverse effects and posioning	48864	61579
01D	Adverse effects and posioning	91356	61579
01G	Adverse effects and posioning	121482	61579
01M	Adverse effects and posioning	114102	61579
01N	Adverse effects and posioning	10377	61579
01W	Adverse effects and posioning	75541	61579
01Y	Adverse effects and posioning	27386	61579
02A	Adverse effects and posioning	3720	61579
02H	Adverse effects and posioning	133236	61579

The average for the 12 would be 61579, the 61579 is what we’d want displayed in the column.  The idea being, we could compare the sum of the cost for the CCG in the chart against the average sum of costs across the selections.

But (where it gets messier), if we choose a commissioner_desc from the filter, we’d expect the average of sums to still be of the 12 ccgs, not just the ‘one’ selected and displayed in the chart.

Any Ideas please

best regards

Andy

[awhitfield]

Nice one Sunny, not surprised it was causing a brain explosion!

Many Thanks

Best Regards

Andy

[awhitfield]

Hi Sunny,

thanks again for the solution, but cpildyou break down and explain how the expression actually works?

Thanks in advance

best Regards

Andy

[sunny_talwar]

So I basically I am summing the value for each commissioner_code and programme_Category and ignoring the selection in commissioner_code

Sum({<commissioner_code>}Value)

Next we needed the Avg across all the commissioner_code, so used TOTAL Qualifier with the field name yet again ignoring selection in commissioner_code.

Avg({<commissioner_code>} TOTAL <programme_category> Aggr(Sum({<commissioner_code>}Value), commissioner_code, programme_category))

Now, since I am ignoring selection in commissioner_code, any selection in commissioner_code will keep showing all the rows. In order to only show the single selected row, but still show the overall average, I multiplied it with Avg(1). Avg(1) will equal 1 for selected commissioner_code and will equal 0 for all other making them all 0.

Does this help?

[awhitfield]

Superb!