Forum Discussion

harrinho's avatar
harrinho
Icon for Helper III rankHelper III
8 years ago
Solved

Calculate SUM with Multiple Criteria

Hi Community, 

 

I want to create a measure which will calculate the price of my line items (opportunities) when: 

Family_type = "Product" AND business_type_name="New" and Closed Pipeline="Open"

 

I tried the following but it never worked.  

 

4Q TCV = CALCULATE(SUM('FACT_PIPELINE'[SalesPrice],IF(AND('FACT_PIPELINE'[Family]= "Product",'FACT_PIPELINE'[business_type_name]= "New",'FACT_PIPELINE'[Closed Pipeline]="Open")

Is there any way to achieve this?

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi

    Try this.

    4Q TCV = 
CALCULATE(SUM(FACT_PIPELINE[SalesPrice]),
                     FILTER (FACT_PIPELINE, FACT_PIPELINE[Family]= "Product"),
                     FILTER (FACT_PIPELINE,FACT_PIPELINE[business_type_name]= "New"),
                     FILTER (FACT_PIPELINE,'FACT_PIPELINE'[Closed Pipeline]="Open")
         )

    Thanks

     

    Raj

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi

    Try this.

    4Q TCV = 
CALCULATE(SUM(FACT_PIPELINE[SalesPrice]),
                     FILTER (FACT_PIPELINE, FACT_PIPELINE[Family]= "Product"),
                     FILTER (FACT_PIPELINE,FACT_PIPELINE[business_type_name]= "New"),
                     FILTER (FACT_PIPELINE,'FACT_PIPELINE'[Closed Pipeline]="Open")
         )

    Thanks

     

    Raj

    • philpringuer's avatar
      philpringuer
      Frequent Visitor
      7 years ago

      Thanks Raj!  Answered a question of mine, too - your contribution is appreciated.

    • croufspesi's avatar
      croufspesi
      Frequent Visitor
      6 years ago

      Anonymous hey hey!  stumbled across this old thread and am using your recommendation below.  It's been very helpful to me already -- thanks!!!

       

      One other question -- can you show me how to make one of the filters an AND statement?  

       

      I've tried using && but can tell it wasn't quite the right placement.  Basically just looking for some of my rows to have 2 criteria that are mandatory in order to be included in the SUM.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I have been trying to utilize this same concept to sum all rows that meet multiple VAR critera, but its not quite working. What I am trying to do is a calculation of the last 4 weeks of sales. If I use only one variable I am able to bring data for only one week, but if i add another filter criteria to take into consideration an additional week it shows blank. 

       

      Any ideas?

       

       

       

  • drewlewis15's avatar
    drewlewis15
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    When using the CALCULATE function, you do not need to add the IF and AND functions.   The CALCULATE function has filter syntax built in.  CALCULATE(<measure expression>, <filter1>, <filter2>, ...)

     

    So your statement should read:

     

    4Q TCV = CALCULATE(SUM('FACT_PIPELINE'[SalesPrice]),'FACT_PIPELINE'[Family]= "Product",'FACT_PIPELINE'[business_type_name]= "New",'FACT_PIPELINE'[Closed Pipeline]="Open")