Dax formula to calculate percentage between ranges

[Anonymous]

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

[AlB · Accepted 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"
    )

[Anonymous]

Thanks! That is perfect!