Return sum based on row entry

[Anonymous]

My dataset looks like the following:

Item	Segment	Total sales
Watermelon	Expensive	100
Strawberry	Expensive	200
Banana	Cheap	300
Orange	Cheap	200

I'd like to create a measure that calculates the total sales for that Segment depending on what Item I choose. Here is the desired result:

Item	Segment total
Watermelon	300
Strawberry	300
Banana	500
Orange	500

How do I go about doing this? I basically need a SUM(...) , ALLEXCEPT(... Segment..), but I need to find the correct segment based on the Item (e.g., Watermelon is "Expensive" but Banana is "Cheap").

Thank you!

[jdbuchanan71 · Accepted solution]

@Anonymous 

A variable can read the Segment and we can use that in the measure.

Segement Total = 
VAR _Segment = SELECTEDVALUE('Table'[Segment])
RETURN
CALCULATE(
    SUM('Table'[Total sales]),
    ALLEXCEPT('Table','Table'[Segment]),
    'Table'[Segment] = _Segment
)

[Anonymous]

Thank you so much! This is awesome.