Forum Discussion
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
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.
If you need additional help, please share your desired output.
Best Regards,
Cherry
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'))
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'
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.
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")
