Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
)
Thank you so much! This is awesome.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |