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

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