Calculate Ratio in DAX ( Normalization)

[Asamadi]

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 measure:

Ratio = SUMX(FILTER(bb,bb[CASH PREPEYMENT]<>BLANK()),1-(bb[CASH PREPEYMENT]-MIN('bb'[CASH PREPEYMENT]))/(MAX('bb'[CASH PREPEYMENT])-MIN('bb'[CASH PREPEYMENT])))

this is my file's link:  https://www.dropbox.com/s/h87fn0n0tczqj1c/Question.pbix?dl=0

i can solve it in excel like image below:

 but i colundt solve it in power bi with dax

 

[Anonymous · Accepted solution]

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]

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]

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?