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 fi...
Need Help
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" )
    )
    • Waseem's avatar
      Waseem
      Icon for Helper III rankHelper III
      8 years ago

      Hi Thomas

       

      I have following model linked from excel:

       

      I am trying to sumup the BILLAMOUNT column by filtering out three items (highlighted in yellow) from column "DESCRIPTION". The results are fine when I do not apply any filters. However, when I apply filters on the "Equip ID" column, which is linked to another amapping file, I get messed up numbers. Appreciate your help.

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi Waseem

        "when I apply filters on the "Equip ID" column, which is linked to another amapping file, I get  messed up numbers. "

        what is the another amapping file, is it a table which having a relationship with this table?

        What is the relationship between this table and "another amapping file"?

         

        Best Regards

        Maggie