DAX calculate ratio

[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

[v-jiascu-msft]

Hi @Asamadi,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, split your numerator and denominator into VAR statements and then use RETURN to display one or the other and see where the problem is. You can use DIVIDE with the 3rd parameter to get around errors but I'm assuming you are expecting a ratio and not getting one. Otherwise, provide some sample data and can try to replicate.

[Asamadi]

I have updated my recent post

[v-jiascu-msft · Accepted solution]

Hi @Asamadi,

Try the measure below.

Measure =
VAR minCP =
    CALCULATE ( MIN ( bb[CASH PREPEYMENT] ), ALL ( bb ) )
VAR maxCP =
    CALCULATE ( MAX ( bb[CASH PREPEYMENT] ), ALL ( bb ) )
RETURN
    IF (
        ISBLANK ( MIN ( bb[CASH PREPEYMENT] ) ),
        BLANK (),
        1
            - DIVIDE ( MIN ( bb[CASH PREPEYMENT] ) - minCP, maxCP - minCP )
    )

Best Regards,

Dale