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.
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
Solved! Go to Solution.
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.
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.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |