Table of Contents
In Power BI, data normalization refers to the process of transforming data to a standardized scale, typically between 0 and 1, to allow for meaningful comparison and analysis. This is achieved by using mathematical techniques such as min-max scaling or z-score normalization. By normalizing data in Power BI, users can effectively eliminate differences in scale and magnitude, making it easier to identify patterns and trends in the data. This helps to improve data accuracy and enables more accurate decision-making. To normalize data in Power BI, users can utilize built-in functions or external tools to apply normalization techniques to their data sets.
Power BI: 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
- 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]) RETURNDIVIDE(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]) RETURNDIVIDE(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: