Forum Discussion

pzinsli's avatar
pzinsli
Regular Visitor
8 years ago

Calculating a Filtered Sum

HI    Let's say I have a table like this, table titled PROFIT AND LOSS DETAILS   ACCT NAME               Line Amount Revenue                      20 COGS                           12 Fringe   ...
Need Help
Aron_Moore's avatar
Aron_Moore
Solution Specialist
8 years ago

Try either:

= CALCULATE(SUM('PROFIT AND LOSS DETAIL'[Line Amount]);[ACCT NAME]='Revenue' ; [ACCT NAME]='Cost of Goods Sold')

 

Or the more explicit

= CALCULATE(SUM('PROFIT AND LOSS DETAIL'[Line Amount]);filter('PROFIT AND LOSS DETAIL'; [ACCT NAME]='Revenue' ; [ACCT NAME]='Cost of Goods Sold'))

 

  • pzinsli's avatar
    pzinsli
    Regular Visitor
    8 years ago

     Thanks for your heIp. tried both.  It seems like the formatting causes the program to try to read "revenue" and Cost of Goods Sold" as tables, not a column result being used as a filter.  It reads error message : could not find table 'revenue'

    • Aron_Moore's avatar
      Aron_Moore
      Solution Specialist
      8 years ago

      Odd.


      When you're entering the DAX does the autocomplete help at all?

       

      I did notice your first formula example tries filter[ACCT NAME]='Revenue' which won't work as filter requires a table name not column, but my two example should have correct syntax.