I have 2 table charts.
one is for average value by group
Group
| Average |
|---|
| A | //this value is calculated by the selection of date range |
| B | //this value is calculated by the selection of date range |
| C | //this value is calculated by the selection of date range |
expression for Average column is like this: Avg({$<[money]={">0"}>} [money]) //check 'money' for the value that has null or 0 values
since the value of 'Average' column is calculated dynamically with the selection of date range, I can't set it as a static variable on loading the data.
another table is for utilizing the calculated value of Average from above table chart.
below is the table when date range 2/20~2/22 is selected. so the corresponding Avererage value should be 6 for group A and 7 for group B.(there is no data for group C in selected range, so don't need to consider it)
| Group | date | original value | original value + Average of Group |
|---|
| A | 2/20 | 3 | 9 //3+6 |
| A | 2/21 | | //just show nothingm, because there is no original value |
| A | 2/22 | 9 | 15 //9+6 |
| B | 2/20 | 5 | 12 //5+7 |
| B | 2/22 | 9 | 16 //9+7 |
I don't know how to set expression for the 'original value + Average of Group' column.
Is there anybody can help me? It would be really appreciated.
thank you in advance!
