Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |