Calculated column with ALLEXCEPT works in one table but gets error in another table

[Anonymous]

Hello! 

I have a normal calculated column that works in one of my tables, no problem. Then I made a new table with the same data, but an additional table appended and tried to create the same column. This resulted in the error: "A table of multiple values was supplied where a single value was expected."

This is the first, working column:

This is the second one that gets an error, and I don't know why.

The two tables have the same data.

It is the ALLEXCEPT-function that is struggeling.

Thank you so much for your help!

[v-luwang-msft]

Hi @Anonymous ,

Could you pls share your sample data, and remember to remove confidential data.

Best Regards

Lucien

[amitchandak · Accepted solution]

@Anonymous , I think issue is with +values('Year 2022'[Base])

use aggregation like sum/min/max etc

[Anonymous]

@amitchandak Thanks, that sort of worked! But what happens then is that the base gets added for each time (it is a cumulative sum) when it is only supposed to be added once. Do you have any tips on how to fix that?
The measure now looks like this:

Progress = 
VAR WeekInRow = '2022 Yearly'[Week]
VAR Base = '2022 Yearly'[Base]

RETURN
IF(
    '2022 Yearly'[SumOrLatest] = "Latest",
    '2022 Yearly'[Input Value],
    CALCULATE(
        SUM('2022 Yearly'[Input Value]),
        REMOVEFILTERS('2022 Yearly'[Week]),
        '2022 Yearly'[Week] <= WeekInRow,
        ALLEXCEPT( '2022 Yearly' ,'2022 Yearly'[Week],'2022 Yearly'[Key Result] )
    ) + Base
)

 I had to make a variable out of the base value because otherwise I could not get the one from the row it was supposed to calculate.