Table of Contents
Data can be normalized between 0 and 1 in Power BI by using the “Normalize” function in the “Transform Data” tab. This function scales the data to a range of 0 to 1 by subtracting the minimum value from each data point and then dividing it by the range (maximum value – minimum value). This ensures that all data points are proportionally represented on a scale of 0 to 1, making it easier to compare and analyze data regardless of the original values.
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
- xi: The 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:
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: