I am trying Amazon Quicksight but I don't understand if this is possible. I should display a number that is calculated:
[(a-b) / c]
a - is chosen from a list of data in the column A b - is the mean of the column B c - is the mean of the column C
it's possible?
Thanks
Where a differs depending on the row in column A? I don't think this is possible as you are writing a formula using both aggregated fields (mean of b or c) and a non-aggregated field (a).
I tried the formula with both and got the following error (using the avg function):
Mismatched aggregation. Custom aggregations can’t contain both aggregate "
AVG" and non-aggregated fields “AVG("ColumnId-2")”, in any combination.
@Occamatic is right about inability to use both aggregated fields and a non-aggregated field in your formula.
However, you can circumvent this by using 'a' in an aggregated function in your calculated field. Example:
( sumIf({a},{a}={a}) - b ) / c
Please amend to the specifics to your dashboard, possibly with use of parameters in ifelse statements, but a version of this should work.
For instance, I myself can't use:
ifelse({metric_type}='Averages',avg({metric_value}),sum({metric_value}))
Instead I use:
ifelse(avgIf({metric_value},{metric_type}='Averages') > 0,avg({metric_value}),sum({metric_value}))