- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Sum Overlapping Values In a Table to Remove from T...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

KadenB

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2018
04:04 AM

I am looking for a solution to sum values from one table.

Suppose I have the table shown below:

If I want to sum the amount of money raised by helmet purchases and bike perchases seperately I would get this:

Helmets: 140 and Bikes: 350. However, what I want is to remove the amount of money made by people who baught both a helmet and a bike, which in this case PersonID one is the only person who bought two different items. Here then my Helmet sum would be 100, and my Bike sum would be 250.

I am able to count how many people overlap between the two groups with this post https://community.powerbi.com/t5/Desktop/Calculating-overlap-between-selected-groups/td-p/128624, however I am having trouble getting the sum of the Amount column. Any suggestions would be much appreciated?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Baskar

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2018
05:17 AM

1. Create one calculated Column

Flag =

var _pID= Overlapping[PersonID]

return IF( ISBLANK(CALCULATE(SUM(Overlapping[Amount]),

FILTER(Overlapping,Overlapping[PersonID]=_pID && Overlapping[Product]<>EARLIER(Overlapping[Product])))),1,0)

2. Create Measure for Bike and Helmet

Bike = CALCULATE(SUM(Overlapping[Amount]),FILTER(Overlapping,Overlapping[Flag]=1 && Overlapping[Product]="Bike"))

Helmet = CALCULATE(SUM(Overlapping[Amount]),FILTER(Overlapping,Overlapping[Flag]=1 && Overlapping[Product]="Helmet"))

4 REPLIES 4

Baskar

Super User

Re: Sum Overlapping Values In a Table to Remove from Total Sum

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2018
04:26 AM

Need more clarity on your requirement.

As per my understanding you want to remove the product who bought two different items ? If yes which product we want to remove.

KadenB

Frequent Visitor

Re: Sum Overlapping Values In a Table to Remove from Total Sum

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2018
04:42 AM

Hi, I want to remove the amount raised by people who bought two different items. So the sum of the amount raised I want is the sum for only people who bought either helmets or bikes and not both. So if I look at how much money was generated by Helmets I would get a sum that includes only people that bought helmets and not people that bought both helmets and bikes if that makes sense?

Baskar

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2018
05:17 AM

1. Create one calculated Column

Flag =

var _pID= Overlapping[PersonID]

return IF( ISBLANK(CALCULATE(SUM(Overlapping[Amount]),

FILTER(Overlapping,Overlapping[PersonID]=_pID && Overlapping[Product]<>EARLIER(Overlapping[Product])))),1,0)

2. Create Measure for Bike and Helmet

Bike = CALCULATE(SUM(Overlapping[Amount]),FILTER(Overlapping,Overlapping[Flag]=1 && Overlapping[Product]="Bike"))

Helmet = CALCULATE(SUM(Overlapping[Amount]),FILTER(Overlapping,Overlapping[Flag]=1 && Overlapping[Product]="Helmet"))

KadenB

Frequent Visitor

Re: Sum Overlapping Values In a Table to Remove from Total Sum

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2018
05:49 AM

Thank you very much for your answer. This works correctly for what I asked. I was wondering if its possible to push the solution a step further and base the overlap as to whether or not they bought two different items in the same time period versus whether or not they bought two different items period. I had been trying to work on a solution for this, currently I have something like this:

I manged to make a fomula that I believe is close, however because of the filter at the end of product type Helmet, it returns me the same value as calculating all helmet sales regardless of overlap. I'm wondering if there is a better solution?

Amount raised only from helmets =

SUMX( CALCULATETABLE( SUMMARIZE(Overlap,Overlap[* PersonId],Overlap[Sale Date],Overlap[Amount ],"Amount", IF(Overlap[Sale Date]<= MAX('Date'[Date]) && Overlap[Sale Date] >= MIN('Date'[Date]) && DISTINCTCOUNT(Overlap[Product])=1,SUM(Overlap[Amount]))), FILTER(Overlap,Overlap[Product] ="Helmet")),[Amount])