Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.