Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |