cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Return sum based on row entry

My dataset looks like the following:

ItemSegmentTotal sales
WatermelonExpensive100
StrawberryExpensive200
BananaCheap300
OrangeCheap200

 

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:

 

ItemSegment total
Watermelon300
Strawberry300
Banana500
Orange500

 

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!

1 ACCEPTED SOLUTION
Super User II
Super User II

@hi_potato_salad 

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
)

jdbuchanan71_0-1601580158762.png

 

 

View solution in original post

2 REPLIES 2
Super User II
Super User II

@hi_potato_salad 

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
)

jdbuchanan71_0-1601580158762.png

 

 

View solution in original post

Thank you so much! This is awesome. 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors