CODEWITHSUNDEEP
tableau-api
glonn
glonn

Reputation: 159

How to normalize data to a range in Tableau

In Tableau, I am trying to implement the following normalization logic:

https://stats.stackexchange.com/questions/70801/how-to-normalize-data-to-0-1-range

Basically, I want to take all values from a particular measure and scale them to values in the range of [0, 1]. This requires gathering the minimum and the maximum of a measure. What I want is...

(x - min(x)) / (max(x) - min(x))

If you create a calculated field using the above link's method, you get the "Cannot mix aggregate and non-aggregate" error.

This is where I am stuck. Is there a function and/or trick to get this to work?

Upvotes: 4

Views: 9478

Answers (2)

Ben Fairbank
Ben Fairbank

Reputation: 41

You need to use LOD expressions to tell Tableau that you want the min of all the x values, not just that row. Try:

    (x -{FIXED : MIN(x)})
    /
    ({FIXED  : MAX(x)}-{FIXED  : MIN(x)})

Upvotes: 4

oW_
oW_

Reputation: 156

You can try something like

(MEDIAN(X)- TOTAL(MIN(X))) / (TOTAL(MAX(X)) - TOTAL(MIN(X))).

If the function only acts on one data point at a time, MEDIAN(X) will just return the value of the data point itself.

Upvotes: 2

Related Questions