Maximum of a measure column-wise

[Anonymous]

Hello all,

I am stuck with one simple issue in DAX.

I have a measure to find employees' scoring. I used a measure cause I want to see it change per days, and it does. But now I need to normalize that scoring, and I selected the simpliest of (x-min)/(max-min). BUT here the problem comes: when I try to find the maximum of that measure, it is refined per employee, because measures work rows-wise, not column-wise:

Employee  Scoring MaxScoring (MAXX(Table, [Scoring])

A               30           30

B               40           40

C               60           60

While what I need is actually

Employee  Scoring MaxScoring   MinScoring  NormalScoring((Scoring-MinScoring)/(MaxScoring-MinScoring))

A               30           60                 30                (30-30)/(60-30)=0

B               40           60                 30                (40-30)/(60-30)=0.3

C               60           60                 30                (60-30)/(60-30)=1

Of course, I could find Max and Min over selected period manually, but I need this to be changing dynamically and within 0-1 range.

Any help is appreciated. 

Thank you and kind regards,

Taisiya

[sdjensen]

Hi @Anonymous,

You could try something like this... (you may need to replace ; with , in your DAX formula depending on your location)

MaxScoring = 
MAXX( 
    ADDCOLUMNS(
        ALLSELECTED( Table1[Employee] );
        "_scor"; [Scoring]
    );
    [_scor]
)

MinScoring = 
MINX( 
    ADDCOLUMNS(
        ALLSELECTED( Table1[Employee] );
        "_scor"; [Scoring]
    );
    [_scor]
)

NormalScoring = DIVIDE([Scoring] - [MinScoring]; [MaxScoring] - [MinScoring] )

[Anonymous]

I still get maximum/minimum value per row, not among all employees 😞

[v-yuta-msft]

Hi taisiya,

"Of course, I could find Max and Min over selected period manually, but I need this to be changing dynamically and within 0-1 range."

<--- What does dynamically mean in your requirement? Could you share more details about your requirement?

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft,

What I mean is that I try to calculate scoring per employee per dynamically changing time period, so I cannot just use 'static' calculated columns rather than I need a function to calculate normalized scoring per day, per 2 days, per week.... per period selected on a timeline. I hope it is more clear now.