Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pzinsli
Regular 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
v-piga-msft
Resident Rockstar
Resident Rockstar

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.
drewlewis15
Solution Specialist
Solution Specialist

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")

Aron_Moore
Solution Specialist
Solution Specialist

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.