Value for a column within a table, filtering another column's value on the same table

[nickc_innova]

I've been at it for a few hours and searched around the forums - closest I have come to a similar problem/solution involves time intelligence and trying to find values via PREVIOUSYEAR(). 

I have a single table with names, values and column for previous name. I am trying to calculate the corresponding previous value. 
The table looks something like this:

Note the far right column is what I am trying to return. 

My current code looks like this:

Previous Worker Value =

VAR __prev_worker = 'Table'[Previous Worker]

VAR __jobid = 'Table'[Job id]

VAR __cur_val = CALCULATE(MAX('Table'[Value]))

VAR __prev_val = CALCULATE(__cur_val, ALLEXCEPT('Table', 'Table'[Job id]),

    FILTER(

        ALLNOBLANKROW ( 'Table'[Assigned Worker] ), 'Table'[Assigned Worker]=__prev_worker

    )

)

RETURN

__prev_val

However, this is only returning values for me that are equal to the current value, not the previous worker's value for the corresponding job id. Like this:

[Jihwan_Kim]

Hi,

I assume you want to create a calculated column.

Please check the below picture and the DAX formula.

Previous Worker Value CC =
LOOKUPVALUE (
    Data[Value],
    Data[Assigned Worker], Data[Previous Worker],
    Data[Job ID], Data[Job ID]
)

[nickc_innova]

This results in an error. "A table of multiple values was supplied where a single value was expected". 
For some Jobs, the assigned worker is reported more than once with different values. So, when looking up the previous worker, we get more than one result. 
... need some way to summarize values where job and assigned worker or previous worker match

[tamerj1 · Accepted solution]

Hi @nickc_innova 
Please refer to attached sample file with the solution

Previous Worker Value = 
CALCULATE ( 
    SUM ( 'Table'[Value] ),
    TREATAS ( { 'Table'[Previous Worker] }, 'Table'[Assigned Worker] ),
    ALLEXCEPT ( 'Table', 'Table'[Job Id] )
)

[nickc_innova]

@tamerj1, thank you so much! Not only did you provide a perfect solution but you also introduced me to something new. I did not know about the TREATAS function. Very interesting.