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.
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?
Solved! Go to Solution.
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" )
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
that worked, thank you!
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?
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 |
---|---|
54 | |
25 | |
23 | |
15 | |
11 |
User | Count |
---|---|
77 | |
61 | |
47 | |
18 | |
12 |