Forum Discussion

KadenB's avatar
KadenB
Frequent Visitor
7 years ago
Solved

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 sepera...
Need Help
  • Baskar's avatar
    Baskar
    7 years ago

    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"))

Baskar's avatar
Baskar
Resident Rockstar
7 years ago

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's avatar
KadenB
Frequent Visitor
7 years ago

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?