How can I replicate Tableau column totals using average

Question & Answer
,
1

I’m trying to replicate Tableau’s column total using Average feature.

image
image606×516 54.2 KB

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.

I want the average after replacement

2

What is your calculated field?

Does something like this work?

avgOver(avg(ifelse(isNull({client_id[users]}),{int bearable},{arrival_timestamp_int})))

3

Hi @Max ,

Thank you for the response. My calculated field is like this

ifelse(

(isNull(avgOver(sum({overall}))) OR avgOver(sum({overall})) = 0)

, sumOver(sum({field_1}*{Individual Weight - Final}))/sumOver(sum({Individual Weight - Final}))

, sumOver(sum({overall}*{Individual Weight - Final}))/sumOver(sum({Individual Weight - Final}))

)

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

4

any suggestions here @Max ?

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.

here’s the tableau calc field:

{ INCLUDE [Title, Season], [Country]:
IF ISNULL(AVG([Rating Overall Tb]))
THEN SUM([Rating Ep1 Tb][Individual Weight - Final])/SUM([Individual Weight - Final])
ELSEIF AVG([Rating Overall Tb]) = 0
THEN SUM([Rating Ep1 Tb][Individual Weight - Final])/SUM([Individual Weight - Final])
ELSE SUM(IF ISNULL([Rating Overall Tb]) THEN [Rating Ep1 Tb][Individual Weight - Final] ELSE [Rating Overall Tb][Individual Weight - Final] END)/SUM([Individual Weight - Final])
END}

5

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! :slight_smile:

6

@jemin Have you looked into PRE_AGGs?

ifelse(

(isNull(avgOver(sum({overall}))) OR avgOver(sum({overall})) = 0)

, avg(sumOver(({field_1}*{Individual Weight - Final}),PRE_AGG)/sumOver(({Individual Weight - Final}),PRE_AGG))

, avg(sumOver(({overall}*{Individual Weight - Final}),PRE_AGG)/sumOver(({Individual Weight - Final}),PRE_AGG))

)

1 Like
7

@Max , I had tried PRE_AGG too earlier, but was not getting the result I was expecting.

Th above calculation you provided though throws error that sumOver requires but getting … field_1 * weight would give decimal result only.

@Kristin , thank you for following up… I do look forward to getting some help as this is a major blocker for one of our projects.

8

Hi @jemin

What is the result you are getting now with it versus what you are expecting?

9

Hi @jemin

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.

10

Hi @Max ,

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.

1 Like
11

Hi @jemin

Thanks for reaching out. Yes, quicksight does have certain limitations and a lot of times you will need to do SQL work.

Thanks for providing your solution.