Essentially what I want is column average, but in that column I have ifelse statement - when the value in field A is NULL or 0 then take value from field B, else take field A.
When I do avgOver or sumOver to get average… At row level is fine but at overall aggregated level the calculation skips the part of replacing NULL values and just takes NON NULL values from field A divided by the count of rows in the column for average.
which works fine at row level, but at aggregated level (when I want to show column average) it gives incorrect results.
Since it only takes into account the non-null overall values and not the replaced null values.
Essentially I want an average over this calculated field
I figured out an alternative way in Tableau… If I use include LOD function that’ll give the same result as total using average…but not sure how I can replicate that include in Quicksight.
Hey @jemin! Hopefully you will hear some suggestions from the Community soon. Just in case, I have pinged one of our SAs to see if they can reply back on Monday! Have a good weekend!
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 3 days, we will archive the question.
I don’t think I was able to find a solution for this on quicksight. To get things moving, I just made some flags through sql code and used those to calculate the correct column averages.