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

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

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