cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

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!

vluwangmsft_0-1624598093044.pngvluwangmsft_1-1624598109525.pngvluwangmsft_2-1624598118889.png

Wish it could help you!

 

 

Best Regards

Lucien

 

View solution in original post

6 REPLIES 6
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

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!

vluwangmsft_0-1624598093044.pngvluwangmsft_1-1624598109525.pngvluwangmsft_2-1624598118889.png

Wish it could help you!

 

 

Best Regards

Lucien

 

View solution in original post

Anonymous
Not applicable

@v-luwang-msft 

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 !

v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

OK,I see. Try like below steps:

Step1,unpivot the base table:

vluwangmsft_0-1624521182655.png

vluwangmsft_1-1624521212987.png

 

Step 2,calculate the average:

average = CALCULATE(ROUND(AVERAGE(Table1[Value]),1),ALLEXCEPT(Table1,Table1[Attribute],Table1[Name],Table1[vlauetype]))

vluwangmsft_2-1624521270445.png

 

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)

vluwangmsft_3-1624521389768.png

Final  create the visualization:

vluwangmsft_4-1624521416845.png

 

You could download my pbix file if you need!

Wish it is helpful for you!

 

 

Best Regards

Lucien

Anonymous
Not applicable

@v-luwang-msft 

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 !

v-luwang-msft
Community Support
Community Support

Hi @Anonymous  ,

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?

vluwangmsft_0-1624516493644.png

 

And how to get the final result that   John speed value1=1 and  Karl speed value=1?

 

 

Best Regards

Lucien

Anonymous
Not applicable

Hi @v-luwang-msft ,

 

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:

 JohnKarlAnna
Speed606040
Stamina47.52545

 

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

 

Table 2:

 MinMax
Speed4060
Stamina2547.5

 

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

So we do :

(value-min)/(max-min)

Table 3:

 JohnKarlAnna
Speed110
Stamina100.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.

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!