Forum Discussion
Return sum based on row entry
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!
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 )
2 Replies
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 )