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!
