Power BI: How to Normalize Data Between 0 and 1


To normalize values in a dataset to be between 0 and 1, you can use the following formula:

zi = (xi – min(x)) / (max(x) – min(x))

where:

  • zi: The ith normalized value in the dataset
  • xiThe ith value in the dataset
  • min(x): The minimum value in the dataset
  • max(x): The maximum value in the dataset

To create a new column of normalized values in Power BI, you can use the following syntax in DAX:

Normalized Points = 
VAR Xi = 'my_data'[Points]
VAR MinValue = MIN('my_data'[Points])
VAR MaxValue = MAX('my_data'[Points])
RETURN DIVIDE(Xi - MinValue, MaxValue - MinValue)

This particular example creates a new column named Normalized Points that represents the normalized values of the Points column of the table named my_data.

The following example shows how to use this formula in practice.

Example: How to Normalize Data Between 0 and 1 in Power BI

Suppose we have the following table in Power BI named my_data that contains information about points scored by basketball players on various teams:

Suppose we would like to normalize each of the values in the Points column so that each value ranges between 0 and 1.

To do so, click the Table tools tab along the top ribbon, then click the New column icon:

Then type in the following formula into the formula bar:

Normalized Points = 
VAR Xi = 'my_data'[Points]
VAR MinValue = MIN('my_data'[Points])
VAR MaxValue = MAX('my_data'[Points])
RETURN DIVIDE(Xi - MinValue, MaxValue - MinValue)

This will create a new column named Normalized Points that contains the normalized values of the Points column of the table:

Power BI normalize data

Notice that each of the values in the Normalized Points column range between 0 and 1.

Notice that the first value in the Normalized Points column is 0.60869.

Here is how this value was calculated:

  • zi = (xi – min(x)) / (max(x) – min(x))
  • zi = (22 – 8) / (31 – 8)
  • zi = 14 / 23
  • zi = 0.60869

Each value in the Normalized Points column was calculated in a similar manner.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

How to Count Number of Occurrences in Power BI
How to Use “If Contains” in Power BI
How to Write an IF Statement in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *