Forum Discussion

Crav888's avatar
Crav888
Frequent Visitor
8 years ago
Solved

Sum Multiple when filtered

Hi, 

 

I have the following data set:

 

Area  Item  Cost      

1        Yes    5

1        Yes    10

1        No    20

2        No    12 

2        Yes    13 

2        Yes     12

 

How do I achieve the following:

Sum of cost where Item is "yes" divided by Sum of cost with both "yes" and "no"?

 

It should be so easy, but I am afraid I simply can't crack it, and it is rather urgent.

 

Thank you in advance,

And happy holidays :D

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this

     

    =CALCULATE(SUM(Data[Cost]),Data[Item]="Yes")/SUM(Data[Cost])

     

    Hope this helps.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this

     

    =CALCULATE(SUM(Data[Cost]),Data[Item]="Yes")/SUM(Data[Cost])

     

    Hope this helps.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Crav888,

     

    Another try:

    Measure =
CALCULATE ( SUM ( 'table3'[Cost] ), 'table3'[Item] = "Yes" )
    / CALCULATE ( SUM ( table3[Cost] ), ALL ( table3[Item] ) )

    Best Regards,

    Dale

  • mehaboob557's avatar
    mehaboob557
    Resolver III
    8 years ago

    Hi Crav888,

     

    You have to create Measure with FIlter then like below,

     

    Measure Sum of Yes = CALCULATE(SUMX(Table3,Table3[Cost]),FILTER(Table3,Table3[Item]="YES"))

Measure Sum of NO = CALCULATE(SUMX(Table3,Table3[Cost]),FILTER(Table3,Table3[Item]="NO"))



Divide measure = ([Measure Sum of Yes]/([Measure Sum of Yes]+[
Measure Sum of NO]))

    If it works, Please Like(kudo) my answer and accept as solution.

     

    Regard,

    Mehbub