How to use a grand total value on a calculated field

Question & Answer
,
1

Hi community.
I have a table (like below) showing a calculated field working fine. That table is filter dynamically from other visual using a filter action.

When the table filters, the calculated field is recalculated with the “new data” filtered.
But i needing, no recalculations, just mantains his initial value.

Sample table:

sample-table
sample-table715×373 5.83 KB

Formula for calculated field:

ifelse(
    avg({estimated_monthly_savings_type_1}) >= ({avgOver-savings-type1-calculated}*3),
    "Alto",
    avg({estimated_monthly_savings_type_1}) >= ({avgOver-savings-type1-calculated}*2),
    "Medio",
    "Bajo"
)

Thanks a lot for any help.

2

Hi @rexix8809 - thank you for posting your query.
Since it looks like you’re using avgOver, have you looked into the PRE_FILTER option of calculation level? https://docs.aws.amazon.com/quicksight/latest/user/avgOver-function.html

1 Like
3

Thanks @SD_QS
I’m getting this error when attempting to use PRE_FILTER.

For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated.

Look the formula for AvgOver:

avgOver(
    sum({estimated_monthly_savings_type_1}),
    [],
    PRE_FILTER
)
4

That error is because you’re using sum in your first argument along with PRE_FILTER. Depending on what you’re trying to achieve, remove the sum and the error shouldn’t be there.

I also see that your second argument is blank, what exactly are you trying to average over? the entire dataset?

1 Like
5

Yes, i’m trying to average over the entire dataset. that’s why i attempting to use sum().
But i don’t know how to average all dataset + PRE_FILTER?

Thanks a lot.

6

It will be helpful if you can provide more info around:

  1. What does your dataset look like? What are each rows and the columns you are working with?
  2. What is the end result you’re trying to accomplish?
  3. What are you doing currently? As in what are the different calculated fields you’re using, etc.?
1 Like
7

Hi @rexix8809

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 7 days, we will archive the question.