Calculated field affected the Grand Total in Tableau

Question

Used a calculated field by using the window_max and window_min functions:

IF 
([eCPM]) = WINDOW_MAX(([eCPM])) 
THEN "Least efficient"  ////Red
ELSEIF [eCPM] = WINDOW_MIN(([eCPM]))
THEN "Most efficient CPM"  ///Green
ELSE "Neither"  ///Gray
END

But this also affected my Grand Total. I don't want any coloring in totals. How to handle this? (Solved)
Since my calculation is based upon eCPM, can only this column be highlighted as green rather entire row, without highlighting media cost and visits green as well?

Since my calculation is based upon eCPM, can only this column be highlighted as green rather entire row, without highlighting media cost and visits green as well?

did the answer solved your need? if so, please accept the answer in order to mark the thread as closed — Fabio Fantoni, Sep 07 '21 at 13:36
Since my calculation is based upon eCPM, can only this column be highlighted as green rather entire row, without highlighting media cost and visits green as well? — Esha, Mar 03 '22 at 20:34

score 1 · Accepted Answer · answered Aug 24 '21 at 09:26

You just need to "wrap" you if statement with another one in order to handle the grand total using size which returns the number of rows in the partition.

Using the superstore you can create a calculated field like this:

// Handle Grand Total
IF SIZE() = 1 THEN "Neutral"
// Handle all other "rows"
ELSE
  IF sum([Sales]) = WINDOW_MAX(sum([Sales])) 
    THEN "Green"
  ELSEIF sum([Sales]) = WINDOW_MIN(sum([Sales]))
    THEN "Red"
  ELSE "Neutral"
  END
END

The result could look like this:

Calculated field affected the Grand Total in Tableau

1 Answers1