Forum Discussion

Asamadi's avatar
Asamadi
Helper I
8 years ago
Solved

Calculate Ratio in DAX ( Normalization)

I want to calculate ratio ( for normalization) and i get error NaN how can i solve it? it must be divide (CASH PAYMENT - Min(CASH PAYMENT), Max(CASH PAYMENT) - Min(CASH PAYMENT)) this is my measur...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    I think this has already been done, anyways

    Ratio =
    var minPay = CALCULATE(MIN(bb[CASH PREPEYMENT]); ALL(bb))
    var maxPay = CALCULATE(MAX(bb[CASH PREPEYMENT]); ALL(bb))
    var Val = MAX(bb[CASH PREPEYMENT])
    return
    DIVIDE(Val - minPay; maxPay - minPay; 0)

     

    should give you a number between 0-1. If needed also you can hide where Val is blank.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Give this a try:

 

Ratio =
VAR MaxPeyment =
    MAX ( bb[CASH PREPEYMENT] )
VAR MinPeyment =
    MIN ( bb[CASH PREPEYMENT] )
RETURN
    IF (
        //This checks to see if the denominator in the SUMX will be 0.  The measure will return blank if it is.
        maxpeyment
            <> minpeyment,
        SUMX (
            FILTER ( bb, bb[CASH PREPEYMENT] <> BLANK () ),
            1
                - ( bb[CASH PREPEYMENT] - MinPeyment )
                / ( MaxPeyment - MinPeyment )
        )
    )

We calculating the MAX and MIN values at the beginning and storing them a variables.  This will increase performance.

Asamadi's avatar
Asamadi
Helper I
8 years ago

Thanks For Helping

but when i drop this measure in my table nothing is shown

may you share with me PBI file?

 

is your resalut like my image in Excel?