cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pzinsli Frequent Visitor
Frequent Visitor

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                         100

Engineering                 12

 

 

 

I only want to add the sume of revenue and COGS.  from what I have seen it should look something like 

 

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

5 REPLIES 5
Aron_Moore Established Member
Established Member

Re: Calculating a Filtered Sum

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 Frequent Visitor
Frequent Visitor

Re: Calculating a Filtered Sum

 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 Established Member
Established Member

Re: Calculating a Filtered Sum

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.

drewlewis15 Member
Member

Re: Calculating a Filtered Sum

Try this:

 

Measure = CALCULATE(SUM('PROFIT AND LOSS DETAILS'[Line Amount]),'PROFIT AND LOSS DETAILS'[ACCT NAME]="Revenue" || 'PROFIT AND LOSS DETAILS'[ACCT NAME]= "COGS")

Community Support Team
Community Support Team

Re: Calculating a Filtered Sum

Hi @pzinsli

 

Try this formula below.

 

Measure =
CALCULATE (
    SUM ( 'PROFIT AND LOSS DETAILS'[Line Amount] ),
    FILTER (
        'PROFIT AND LOSS DETAILS',
        'PROFIT AND LOSS DETAILS'[ACCT NAME] IN { "Revenue", "COGS" }
    )
)

Here is the result output. 

 

Capture.PNG

If you need additional help, please share your desired output.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.