Calculate a measure for a total conditioned by other columns

[AliceW]

Hi everyone,

Brain is fried, so please help!

I have this table where all columns are pretty easy to handle with the exception with the last one: Rent (EUR). I've imported it via RELATED using the Opportunity number from a different table. This, however, means a lot of bad data.

Opportunity	Opp Owner	Split Type	Split Owner	Opp Owner = Split Owner	Rent (EUR)
111	Jim	Apples	Jim	Yes	100
111	Jim	Oranges	Jim	Yes	100
111	Jim	Apples	Anne	No	100
111	Jim	Oranges	Anne	No	100
222	Betty	Apples	Betty	Yes	120
222	Betty	Oranges	Betty	Yes	120

So, I would need a Measure (or column, whatever works), to get the Rent money allocated to the Split Owner ONLY if they're also the Opp Owner. For the non-owner, the amount needs to be zero. And oh, that Split Type is doubling/trebling the amounts big time.

This is what I'd love to see:

Opportunity	Split Owner	Rent (EUR)
111	Jim	100
111	Anne	0
222	Betty	120

I've tried a SUMX with a dash of CALCULATE, and I got Anne an amount of 100 😞

Help, please!

Alice

[v-juanli-msft · Accepted solution]

Hi @AliceW 

Create a measure

Measure =
IF (
    MAX ( Sheet8[Opp Owner = Split Owner] ) <> "No",
    LOOKUPVALUE ( Sheet9[value], Sheet9[Opportunity], MAX ( Sheet8[Opportunity] ) ),
    0
)

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AliceW]

Thank you so much, Maggie!!!