When working on a project, I had the Idea that a Radar Chart would be extremely beneficial to the story I wanted to tell. To create a radar chart with all of the points representing different categories all with different scales, I had to normalize the values so they would all be at the same scale.
The data was in the form of.
Where each row was a different test an athlete had undergone on that day. We wanted to normalize across the tests.
To do this I had to find a formula to normalize data, this formula was:
Zi=(Xi-Min(x))/(Max(x)-Min(x))
To start I had to first calculate the min for each category in the example above the values for Test A are 1 & 3 So the Minimum Value is 1 and the Maximum Value is 3.
This is the code that was used to create a Minimum and Maximum Variable across the Tests. We would have to remove the Name and Index Fields as we would want all the other fields to be able to act as a slicer on the PBI View.
VAR Minimum =
CALCULATE(
MIN(Append1[Values])
, REMOVEFILTERS(Append1[Name],Append1[Index])
)
VAR Maximum =
CALCULATE(
MAX(Append1[Values])
, REMOVEFILTERS(Append1[Name],Append1[Index])
)
Append is the Table name as I had to combine 2 different sheets.
(SUM(Append1[Values])-Minimum)/(Maximum-Minimum)
This line of code is the calculation that normalizes the values. As Minimum and Maximum are variables that we have created earlier so it is able to be used in the further line of code.
This code should be usable in any piece of work for those wanting to normalize values where each row of data is a different test.