Forum Discussion
Sum Overlapping Values In a Table to Remove from Total Sum
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?
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
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.
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?
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"))
