cancel
Showing results for
Did you mean: Regular Visitor

## Min-max normalization with averages values

Hello to the team,

I have been trying to do some min max normalization on average value, I am sorry because I have found some resources here but I can't seem to make it work. It's my first time using powerBI but I have some code experience.

My original data is on this format:

 Name Date Attribute Value_1 Value_2 John 2010-01-10 Speed 50 30 John 2010-01-19 Speed 70 50 John 2010-01-10 Stamina 30 40 John 2010-01-19 Stamina 65 40 Karl 2010-01-10 Speed 50 20 Karl 2010-01-19 Speed 70 60 Karl 2010-01-10 Stamina 20 50 Karl 2010-01-19 Stamina 30 15 Anna 2010-01-10 Speed 60 25 Anna 2010-01-19 Speed 20 65 Anna 2010-01-10 Stamina 30 55 Anna 2010-01-19 Stamina 60 15

So as you can see I have several names, and several attributes, for all of those attributes and names I have two key values that have been collected through time, goal is to study those two values independantly to plot them afterwards

What I want to do, is to make a min-max normalization of the average of those attributes per name, and have all of this dynamically change when I reduce the time frame.

If we decompose it, it looks like this:

it's for Value 1 and Value 2 independantly so let's take value_1 for instance:

- Calculate the average value for each attribute, per name.

- Found the max and min of those average for each attribue

- Normalize the averages previously calculated.

Could you help me please ?

Thank you very much

Useful output would be something like this

 Value John Karl Anna Speed Value_1 0 1 0.6 Speed Value_2 0.6 1 0 Stamina Value_1 0.4 0 1 Stamina Value_2 1 0.9 0

1 ACCEPTED SOLUTION  Community Support

Hi @Ambroggio ，

Use the following measures:

``````averagE1 =
VAR averagE1 =
CALCULATE (
ROUND ( AVERAGE ( Table1[Value] ), 1 ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[Name] = MAX ( Table1[Name] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)
RETURN
averagE1``````
``````maxvalue2 =
CALCULATE (
MAXX ( Table1, [averagE1] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)
``````
``````minvalue2 =
CALCULATE (
MINX ( Table1, [averagE1] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)``````
``````Out2 =
ROUND ( ( [averagE1] - [minvalue2] ) / ( [maxvalue2] - [minvalue2] ), 2 )``````

Final you will get what you want!   Best Regards

Lucien

6 REPLIES 6  Community Support

Hi @Ambroggio ，

Use the following measures:

``````averagE1 =
VAR averagE1 =
CALCULATE (
ROUND ( AVERAGE ( Table1[Value] ), 1 ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[Name] = MAX ( Table1[Name] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)
RETURN
averagE1``````
``````maxvalue2 =
CALCULATE (
MAXX ( Table1, [averagE1] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)
``````
``````minvalue2 =
CALCULATE (
MINX ( Table1, [averagE1] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Attribute] = MAX ( 'Table1'[Attribute] )
&& Table1[vlauetype] = MAX ( Table1[vlauetype] )
)
)``````
``````Out2 =
ROUND ( ( [averagE1] - [minvalue2] ) / ( [maxvalue2] - [minvalue2] ), 2 )``````

Final you will get what you want!   Best Regards

Lucien Regular Visitor

Thank you very much that's perfect it works, and I learnt a lot on how PowerBI handles variables.

It's very slow so I might need to look into that later, but this is an another story. Thank you !  Community Support

Hi @Ambroggio ，

OK，I see. Try like below steps:

Step1,unpivot the base table:  Step 2，calculate the average:

``average = CALCULATE(ROUND(AVERAGE(Table1[Value]),1),ALLEXCEPT(Table1,Table1[Attribute],Table1[Name],Table1[vlauetype]))`` Step 3,accord the average value ,new column to get the max value ,and the min value:

``maxvalue = CALCULATE(MAX(Table1[average]),ALLEXCEPT(Table1,Table1[Attribute],Table1[vlauetype]))``
``minvalue = CALCULATE(min(Table1[average]),ALLEXCEPT(Table1,Table1[Attribute],Table1[vlauetype]))``

Final ,accord the column to get out put:

``out = round((Table1[average]-Table1[minvalue])/(Table1[maxvalue]-Table1[minvalue]),2)`` Final  create the visualization: Wish it is helpful for you!

Best Regards

Lucien Regular Visitor

Thank you very much !

I successfully got what I wanted, didn't think I could use customed columns.

I have one additional question, how can I make the table dynamically change with a date slicer ? Maybe slicers can't be used in calculated column, and I should therefore try to convert what you suggested into measures ?

Thank you !  Community Support

Hi @Ambroggio  ，

Not very clearly about how to get the output.

- Calculate the average value for each attribute, per name.

Just like John ,Speed , is  the value is :

（John   2010/1/10 speed  value1+John   2010/1/10 speed  value2+ John   2010/1/19 speed  valu1e+ John   2010/1/19 speed  value2）/4? And how to get the final result that   John speed value1=1 and  Karl speed value=1?

Best Regards

Lucien Regular Visitor

So sorry it wasn't clear. The normalization has to be done on value 1 and value 2 independantly, so let's take value 1 for instance.

First we calculate the average of Value 1 for each of the attribute and for each name.

Table 1:

 John Karl Anna Speed 60 60 40 Stamina 47.5 25 45

Then we need to find the max and min of the averaged values for the normalization

Table 2:

 Min Max Speed 40 60 Stamina 25 47.5

Now that we have those values we can normalize our averages from table 1.

So we do :

(value-min)/(max-min)

Table 3:

 John Karl Anna Speed 1 1 0 Stamina 1 0 0.89

We then do the same thing for value 2 which gives use another set of normalized values.

I hope it's clearer, thank you. Announcements #### Welcome to the User Group Public Preview  