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

 

 

 

 

 

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.

3 Replies

  • 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? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.