Calculated fields aggregating data

Question & Answer
, ,
1

I have a visualization with a calculated field that uses the following formula:

({Importe_A} * 0.7 + {Importe_B}) / {Importe_C}

When aggregating data, I’ve noticed that the value of this field is incorrect. QuickSight calculates the value of the new field for each individual row and then sums them all up to produce the aggregated result.

Does anyone have a solution for this?

2

Hello @elotorre, welcome to the QuickSight community!

You should be able to resolve this by utilizing LAC-W calculations to partition the data appropriately. I’m not sure how you want to group the field values in the formula, but I will assume the values are summed by country before being aggregated.

(sumOver({Importe_A}, [{country}], PRE_AGG)*0.7 + sumOver({Importe_B}, [{country}], PRE_AGG)) / sumOver({Importe_C}, [{country}], PRE_AGG)

Something like this could help ensure you are aggregating the data correctly before it is used in the overall calculation. Let me know if this resolves the issue!

3

Hello @DylanM !
It worked using the following formula:
(sumOver(sum({Importe_A}), [Fecha,{Eje_X},{Eje_Y}], POST_AGG_FILTER) * 0.7 + sumOver(sum({Importe_B}), [Fecha,{Eje_X},{Eje_Y}], POST_AGG_FILTER))

/

sumOver(sum({Importe_C}), [Fecha,{Eje_X},{Eje_Y}], POST_AGG_FILTER)

Thanks a lot for your reply!

1 Like