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 u...
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

     

v-luwang-msft's avatar
v-luwang-msft
Icon for Community Support rankCommunity 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 !