Calculate field: sum of column total relative to (fixed) target column

If I have a table like below with a fixed numerical target:
Total sales can be calculated or showed by column totals,
but how would I then calculate the target ratio as a calculated field,
or should I do this in another way?

It is not possible to something like this: sum(sales)/target_sales, this gives an arror

2022 2021 2020
product target_sales sales sales sales Total sales target_ratio
A 1500 100 200 500 800 53%
B 2000 400 800 500 1700 85%
C 2500 500 600 400 1500 60%

image

Hi roytaas,

You are seeing an error because your target_sales field is not being aggregated in your calculated field.

If you edit your calculated field to have both values be aggregated, like the below example, you should no longer trigger an error.

sum(sales)/sum(target_sales)

Does this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

Ok, the answer is not quite right, but I now understand, how to do it
Thanks to your tip, thanks for that! The calculation should be as follows:
sum(sales)/avg(target_sales)

When you work with sum(target_sales), the calculation goes wrong…

1 Like