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.
Hi experts,
Someone could help me to define a dax formula to calculate the following scenario:
Green: 90% - 100% below target
101% - 110% above target
Yellow: 50% - 89% below target
111% - 120% above target
Red: 0 – 49% below target
> 120% above target
I have two Columns:
A: Current Projection
B: Target Projection
Cheers,
Gilly
Solved! Go to Solution.
Hi @Anonymous
Let's see if I've understood correctly what you are looking for. You can create a new calculated column as follows. Review the conditions at the edges to make sure it's the ranges you want.
NewCol = VAR _Res = DIVIDE ( Table1[Current Projection], Table1[Target Projection] ) RETURN SWITCH ( TRUE (), ( _Res >= 0.9 && _Res < 1.01 ) || ( _Res >= 1.01 && _Res < 1.1 ), "Green", ( _Res >= 0.5 && _Res < 0.9 ) || ( _Res >= 1.1 && _Res < 1.2 ), "Yellow", ( _Res >= 0 && _Res < 0.5 ) || ( _Res >= 1.2 ), "Red" )
Hi @Anonymous
Let's see if I've understood correctly what you are looking for. You can create a new calculated column as follows. Review the conditions at the edges to make sure it's the ranges you want.
NewCol = VAR _Res = DIVIDE ( Table1[Current Projection], Table1[Target Projection] ) RETURN SWITCH ( TRUE (), ( _Res >= 0.9 && _Res < 1.01 ) || ( _Res >= 1.01 && _Res < 1.1 ), "Green", ( _Res >= 0.5 && _Res < 0.9 ) || ( _Res >= 1.1 && _Res < 1.2 ), "Yellow", ( _Res >= 0 && _Res < 0.5 ) || ( _Res >= 1.2 ), "Red" )
Thanks! That is perfect!
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |