cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jjmd Occasional Visitor
Occasional Visitor

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 >= Estimate Value, "Met Target", "Did Not Meet Target") .

 

The issues are, there are many performance measures, many years, and the Actual and Estimate values are in the SAME column. They are differentiated by another column that indicates the value type, "Actual" or "Estimate".

 

My Current Formula is:
Met or Did Not Meet = 

IF( 'Table'[Performance Measure] = 'Table'[Performance Measure] && 'Table'[Year] = 'Table'[Year],
IF( LOOKUPVALUE( 'Table'[Value], 'Table'[Value Type], "Actual") >=  LOOKUPVALUE( 'Table'[Value], 'Table'[Value Type], "Estimate"),
"Met", "Did Not Meet")
)

Error Received = A table of multiple calues was supplied where a single value was expected.

 

What should my dax formula say?

 

Column Headers are: Year, Performance Measure, Value, Value Type (Actual or Estimate).

An Example of some data is:

Table

Year | Performance Measure | Value |  Value Type || "Met Target or Did Not Meet Target"

2016| Average Credit            | 1171   | Estimate

2016| Average Credit            | 1250   | Actual

2016| Number of Customers|   800   | Estimate

2016| Number of Customers|   745   | Actual

2017| Average Credit            | 1200   | Estimate

2017| Average Credit            | 1995   | Actual

2017| Number of Customers| 1000   | Estimate

2017| Number of Customers| 1300   | Actual

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Need help with match function for a calculated column

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" )

445.PNG

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.

 

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Need help with match function for a calculated column

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" )

445.PNG

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.

 

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,816)