Measure not working for calculated column

[Anonymous]

I have written a measure to format a calculated column but am getting an error message.

The measure is "Variance1 = IF('Forecast Expense'[Variance]=-0, 'Forecast Expense'[Variance]*-1, 'Forecast Expense'[Variance])", where "Variance" is the name of the calculated column. This measures is supposed to ensure that any nil result shows as "0%" and not "-0%".

The calculated column is "Variance = calculate(divide(sum('Actual Expense'[Expense Amount])+sum('Forecast Expense'[Forecast Amount]),sum('Initiative Funding'[Amount]))-1)".

The error message is "A single value for column 'Variance' in table 'Forecast Expense' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.".

The formula appears to be wanting a single result, whereas I am looking for a different result for each row. This is consistent with my research, which suggests that  a measure cannot be applied to a calculated column as one uses rows and the other columns.

Is there another way to achieve my objective.

Thanks

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can change the measure using MAX function to get values for every rows like DAX below.

Variance1 = IF(MAX('Forecast Expense'[Variance])=-0, MAX('Forecast Expense'[Variance])*-1, MAX('Forecast Expense'[Variance]))

Best Regards,

Amy

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