Forum Discussion
Adding two measures within an IF statement
Help! I'm trying to create a calculated column using an IF statement and need to add together 2 measures and compare the points value. Both measures are based on the "Points" column. The standard deviation is 12.5 and the average is 25.7 so together, they should equal 38.2. Since some of the point values are greater than 38.2, I would expect those values to have a Tier of "S" but instead, it is showing V for all of them. What am I doing wrong?
The standard deviation and average measures are being computed in the current row context, they are not being computed against the whole dataset.
You'll need something like
Tier = VAR _stdDev = CALCULATE ( [Standard deviation], ALL () ) VAR _avg = CALCULATE ( [Average], ALL () ) RETURN IF ( 'ASG Contribution Points'[Points] > ( _stdDev + _avg ), "S", "V" )
6 Replies
The standard deviation and average measures are being computed in the current row context, they are not being computed against the whole dataset.
You'll need something like
Tier = VAR _stdDev = CALCULATE ( [Standard deviation], ALL () ) VAR _avg = CALCULATE ( [Average], ALL () ) RETURN IF ( 'ASG Contribution Points'[Points] > ( _stdDev + _avg ), "S", "V" )I tried exactly that and am recieving this error:
The syntax for 'stdDev' is incorrect. (DAX(VAR stdDev = CALCULATE('ASG Contribution Points'[Standard Deviation], ALL())VAR avg = CALCULATE('ASG Contribution Points'[Average],ALL())RETURN IF ('ASG Contribution Points'[Points] > (stdDev + avg), "S", "V"))).
can you post a screenshot of the measure definition you are using?
Looks like stdDev and avg are reserved words in DAX, even though they're not function names. Just put an _ before them. I'll edit my original post to include that