Forum Discussion

KG1's avatar
KG1
Icon for Resolver I rankResolver I
6 years ago
Solved

Multiple Filters in a Cumulative SUM Measure

Hi 

 

I have a measure which is calcualting a cumulative SUM 

 

CALCULATE(SUM('Turnover & Contribution 20_21 Actuals'[Value]),FILTER(ALL('Turnover & Contribution 20_21 Actuals'),[Month]<=MAX([Month])),VALUES('Turnover & Contribution 20_21 Actuals'[Contract]))

 

 

I need to add 2 filters to the measure 

 

Column Name = Budget (filter on "F1")

 

Column Name = Type (filter on "Contribution")

 

How do I incorporate multiple filters into the measure?

 

Thank you in advance

Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    KG1 

    Modify your Filter Function as below:

    FILTER(
         ALL('Turnover & Contribution 20_21 Actuals'),
         [Month]<=MAX([Month]) && [Budget] = "F1" && [Type] = "Contribution"
        )

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon on the right if you like this reply 🙂

    YouTube, LinkedIn

3 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    KG1 

    Modify your Filter Function as below:

    FILTER(
         ALL('Turnover & Contribution 20_21 Actuals'),
         [Month]<=MAX([Month]) && [Budget] = "F1" && [Type] = "Contribution"
        )

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon on the right if you like this reply 🙂

    YouTube, LinkedIn

    • KG1's avatar
      KG1
      Icon for Resolver I rankResolver I
      6 years ago

      Great - thank you

       

      Worked perfectly

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    KG1 , not very clear

    Try like

    CALCULATE(SUM('Turnover & Contribution 20_21 Actuals'[Value]),FILTER(ALL('Turnover & Contribution 20_21 Actuals'),[Month]<=MAX([Month])),VALUES('Turnover & Contribution 20_21 Actuals'[Contract]), 'Turnover & Contribution 20_21 Actuals'[Budget] ="F1" , 'Turnover & Contribution 20_21 Actuals'[Type]="Contribution")

     

    Or

     

    CALCULATE(SUMX(filter('Turnover & Contribution 20_21 Actuals', 'Turnover & Contribution 20_21 Actuals'[Budget] ="F1" && 'Turnover & Contribution 20_21 Actuals'[Type]="Contribution") ,'Turnover & Contribution 20_21 Actuals'[Value]),FILTER(ALL('Turnover & Contribution 20_21 Actuals'),[Month]<=MAX([Month])),VALUES('Turnover & Contribution 20_21 Actuals'[Contract]), )