Bubble chart variance expression help

[#1]

Hi I’m hoping someone can help me figure out the correct syntax for an expression in a bubble chart.

With widget type as my dimension, I’d like my X axis to be the count of widgets, the Y axis to be the cost of widgets, and the bubble size expression to be the variance in store cost per widget type.

So I have a table showing each widget cost at each store:

STORES	WIDGETS	STORE-WIDGET	COST
STORE1	WIDGET1	STORE1-WIDGET1	$9
STORE1	WIDGET2	STORE1-WIDGET2	$8
STORE2	WIDGET1	STORE2-WIDGET1	$6
STORE2	WIDGET3	STORE2-WIDGET3	$5
STORE2	WIDGET3	STORE2-WIDGET3	$3
STORE2	WIDGET1	STORE2-WIDGET1	$4
STORE3	WIDGET2	STORE3-WIDGET2	$8
STORE3	WIDGET2	STORE3-WIDGET2	$3
STORE3	WIDGET2	STORE3-WIDGET2	$4
STORE3	WIDGET1	STORE3-WIDGET1	$7
STORE4	WIDGET3	STORE4-WIDGET3	$6

If the average cost of a given widget at a given store is greater than the overall average cost of that widget, the difference is determined and added to a sum:

STORES	WIDGET	Count	TOTAL COST	Avg Cost	If the avg cost of a given widget at a given store is greater than the overall avg cost of that widget, the difference is determined and added to a sum
STORE1	WIDGET1	1	$9	$9.00	$2.50
STORE2	WIDGET1	2	$10	$5.00	$0.00
STORE3	WIDGET1	1	$7	$7.00	$0.50
	WIDGET1	4	$26	$6.50	$3.00
STORE1	WIDGET2	1	$8	$8.00	$2.25
STORE3	WIDGET2	3	$15	$5.00	$0.00
	WIDGET2	4	$23	$5.75	$2.25
STORE2	WIDGET3	2	$8	$4.00	$0.00
STORE4	WIDGET3	1	$6	$6.00	$1.33
	WIDGET3	3	$14	$4.67	$1.33

And so my variance in store cost per widget type would be:

WIDGET	Count	TOTAL COST	Average	Variation
WIDGET1	4	$26	$6.50	$3.00
WIDGET2	4	$23	$5.75	$2.25
WIDGET3	3	$14	$4.67	$1.33

Here is the code I came up with for my expression, but it only works when I filter to a given widget type:

=sum(AGGR( if(sum(COST)/count(DISTINCT WIDGET) > sum( TOTAL COST)/count( TOTAL DISTINCT WIDGET),
sum(COST)/count(DISTINCT WIDGET) - sum( TOTAL COST)/count( TOTAL DISTINCT WIDGET), 0),
STORE-WIDGET))

If I have more than 1 widget type selected, the overall average portion of the expression: sum( TOTAL COST)/count( TOTAL DISTINCT WIDGET)

  returns the overall average of all widgets selected, instead of the overall average for each widget type.

Note that I have tried many variations of this expression with nested Aggr, and/or set analysis, but no luck.

Any help would be very much appreciated. Thanks!

[sunny_talwar · Accepted solution]

Try this:

=Sum(Aggr(RangeMax(Avg(COST) -Avg(TOTAL <WIDGETS> COST), 0), WIDGETS, STORES))

[#3]

Thanks Sunny - Works perfect!