Forum Discussion
Helper IDAX calculate ratio
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
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
4 Replies
- Greg_Deckler
Community Champion
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.
AsamadiI have updated my recent post
v-jiascu-msftMicrosoft Employee
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
- v-jiascu-msft
