Amazon QuickSight - Display number

Question

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

occamatic · Answer 1 · 2021-11-03T10:12:30.697

0

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.

edited Nov 03 '21 at 10:12

answered Nov 03 '21 at 08:30

occamatic

362
3
10

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 03 '21 at 10:03

score 0 · Answer 2 · edited Jan 27 '22 at 14:57

@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}))

Amazon QuickSight - Display number

2 Answers2