Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    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!

    Wish it could help you!

     

     

    Best Regards

    Lucien

     

6 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    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?

     

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

     

     

    Best Regards

    Lucien

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    Hi Anonymous ，

    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:

     

    You could download my pbix file if you need!

    Wish it is helpful for you!

     

     

    Best Regards

    Lucien

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    v-luwang-msft
    Community Support
    5 years ago

    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!

    Wish it could help you!

     

     

    Best Regards

    Lucien

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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 !