4 Replies Latest reply on Feb 13, 2019 2:14 PM by Franco Arda

# Normalizing the data in Tableau

Hi everyone,

I need help in normalizing the data I have. Plaese help me in calculation as I don't know the right method to do it.

Below is the snapshot of my data.

 Product ID Month Sales Unit Sales(in thousands) Profit 1001 Jan-17 288 483 48 1001 Feb-17 683 1502 202 1001 Mar-17 391 652 65 1001 Apr-17 395 664 67 1001 May-17 267 448 45 1001 Jun-17 473 794 79 1001 Jul-17 267 448 45 1001 Aug-17 365 613 61 1001 Sep-17 397 673 68 1001 Oct-17 302 509 51 1001 Nov-17 267 448 45 1001 Dec-17 359 604 60 1002 Jan-17 154 259 26 1002 Feb-17 560 1239 168 1002 Mar-17 395 671 68 1002 Apr-17 231 389 39 1002 May-17 195 328 33 1002 Jun-17 286 481 48 1002 Jul-17 154 259 26 1002 Aug-17 195 328 33 1002 Sep-17 231 389 39 1002 Oct-17 180 302 30 1002 Nov-17 169 285 29 1002 Dec-17 270 454 46

I want to normalize this data. I have done it in Excel.

Calculation steps in Excel: For Sales in months for a unique Product ID, finding the maximum and minimum and using sales- min(sales)/ max(sales)-min(sales).

The desired final output is like this.

 Product ID Month Sales Unit Sales(in thousands) Profit 1001 Jan-17 0.050 0.033 0.022 1001 Feb-17 1.000 1.000 1.000 1001 Mar-17 0.297 0.194 0.126 1001 Apr-17 0.308 0.205 0.137 1001 May-17 0.000 0.000 0.000 1001 Jun-17 0.495 0.328 0.220 1001 Jul-17 0.000 0.000 0.000 1001 Aug-17 0.235 0.156 0.104 1001 Sep-17 0.312 0.213 0.148 1001 Oct-17 0.085 0.057 0.038 1001 Nov-17 0.000 0.000 0.000 1001 Dec-17 0.222 0.148 0.099 1002 Jan-17 0.000 0.000 0.000 1002 Feb-17 1.000 1.000 1.000 1002 Mar-17 0.594 0.421 0.299 1002 Apr-17 0.189 0.132 0.091 1002 May-17 0.102 0.071 0.049 1002 Jun-17 0.326 0.227 0.159 1002 Jul-17 0.000 0.000 0.000 1002 Aug-17 0.102 0.071 0.049 1002 Sep-17 0.189 0.132 0.091 1002 Oct-17 0.064 0.044 0.030 1002 Nov-17 0.038 0.026 0.018 1002 Dec-17 0.285 0.199 0.140

Please, help me create the same in Tableau.

Thank you.

• ###### 1. Re: Normalizing the data in Tableau

Hi, Indrajeet

Please find my solution below by creating a calculation field

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Normalizing the data in Tableau

Hi ZZ,

Thanks a lot !!

This really worked. I was trying to use the fixed function but couldn't do it correctly.

You really saved me a lot of time. Thanks again.

Regards,

Indrajeet

• ###### 3. Re: Normalizing the data in Tableau

Hi Indrajeet,

Looks like you've found an answer. I had a similar problem, only the data I was trying to normalize was already an aggregated field, so the above calculation didn't work. I was, however, able to solve the issue with this calculation:

[Metric]*(1/WINDOW_MAX([Metric]))

I just thought this could be helpful for anyone with a similar issue.

• ###### 4. Re: Normalizing the data in Tableau

Interesting solution.

I approached it slightly differently. MinMax scalign in Python is:
minmax = [(x_i - min(x)) / (max(x) - min(x)) for x_i in x]

Which should be in Tableau the equivalent of Python's MinMaxScale (a.k.a. Normalization):

(MEDIAN([Blood Pressure])- TOTAL(MIN([Blood Pressure]))) / (TOTAL(MAX([Blood Pressure])) - TOTAL(MIN([Blood Pressure])))