Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help with match function for a calculated column

I am trying to build a calculated column using a formula that would say whether a performance measure met or did not meet the target value for a certain year. The logic is: If( Actual Value >= Estim...
Need Help
  • dax's avatar
    dax
    6 years ago

    Hi jjmd, 

    You could use measure like below to achieve your goal

    Measure 2 =
VAR Actual =
    CALCULATE (
        SUM ( 'Table (4)'[ Value ] ),
        FILTER (
            ALLEXCEPT (
                'Table (4)',
                'Table (4)'[Year ],
                'Table (4)'[ Performance Measure ]
            ),
            'Table (4)'[  Value Type ] = "Actual"
        )
    )
VAR Estimate =
    CALCULATE (
        SUM ( 'Table (4)'[ Value ] ),
        FILTER (
            ALLEXCEPT (
                'Table (4)',
                'Table (4)'[Year ],
                'Table (4)'[ Performance Measure ]
            ),
            'Table (4)'[  Value Type ] = "Estimate"
        )
    )
RETURN
    IF ( Actual - Estimate > 0, "Met", "Did Not Meet" )

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

dax's avatar
dax
Community Support
6 years ago

Hi jjmd, 

You could use measure like below to achieve your goal

Measure 2 =
VAR Actual =
    CALCULATE (
        SUM ( 'Table (4)'[ Value ] ),
        FILTER (
            ALLEXCEPT (
                'Table (4)',
                'Table (4)'[Year ],
                'Table (4)'[ Performance Measure ]
            ),
            'Table (4)'[  Value Type ] = "Actual"
        )
    )
VAR Estimate =
    CALCULATE (
        SUM ( 'Table (4)'[ Value ] ),
        FILTER (
            ALLEXCEPT (
                'Table (4)',
                'Table (4)'[Year ],
                'Table (4)'[ Performance Measure ]
            ),
            'Table (4)'[  Value Type ] = "Estimate"
        )
    )
RETURN
    IF ( Actual - Estimate > 0, "Met", "Did Not Meet" )

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.