Forum Discussion

Waseem's avatar
Waseem
Icon for Helper III rankHelper III
8 years ago

Function that does not ignore filters

Dear All

 

I am using following formula to calculate a measure value:

 

Net Amount = SUM([Amount])-CALCULATE(SUM([Amount]),FILTER(ALL('Subs'),[DESCRIPTION]="Rental"))

 

However, when i apply filters in the graphs, all numbers get ruined. Is there an alternative to "ALL" that does not ignore filters but gives same results?

 

Regards 

Need Help

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    HI Waseem

     

    Please try this tweak as a quick test

     

    Net Amount = SUM([Amount])-CALCULATE(SUM([Amount]),FILTER(ALLSELECTED('Subs'),[DESCRIPTION]="Rental"))
    • Waseem's avatar
      Waseem
      Icon for Helper III rankHelper III
      8 years ago

      Hi Phil

       

      I already tried "ALLSELECTED" as well but same impact.

       

      Appreciate though.

       

      Regards 

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Allselected not working could be due to your data model. If you can share more info that may help.

         

        Also try removing the argument from the allselected as follows 

         

        Net Amount =
SUM ( [Amount] )
    - CALCULATE (
        SUM ( [Amount] ),
        FILTER ( ALLSELECTED (), [DESCRIPTION] = "Rental" )
    )