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

Sum if rows contain matching data

I'm working on a travel budget report. I have a 'Budget' table with everyone's last name in Column 1 and their alloted budget in Column 2, like this:

'Budget'

Name             Budget

Anderson       $1,000

 

In a second table I have all the 'Transactions' for the year. The transaction names contain (somewhere in the field) the last name of the related person, i.e.:

'Transactions'

Transaction                          Spend

Anderson cell phone           $100

Hotel charge Anderson       $200

 

I'm trying to create a Column 3 in the 'Budget' table that will sum Spend by Name. In other words, it will look at the Name column in the 'Budget' table, and then scan the Transaction column in the second 'Transactions' table for anything containing that Name, and sum the corresponding Spend. The above example would output this:

'Budget'

Name             Budget      Actual Spend by Name

Anderson       $1,000       $300

 

I've tried several different ways but come up with an error every time. Any help is appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sum if rows contain matching data

Hi @sfink22

Try this for your new column in the 'Budget' table:

 

 

Actual Spend by Name =
CALCULATE (
    SUM ( Transactions[Spend] ),
    FILTER (
        Transactions,
        FIND ( Budget[Name], Transactions[Transaction], 1, 0 ) > 0
    )
)

 

2 REPLIES 2
Super User
Super User

Re: Sum if rows contain matching data

Hi @sfink22

Try this for your new column in the 'Budget' table:

 

 

Actual Spend by Name =
CALCULATE (
    SUM ( Transactions[Spend] ),
    FILTER (
        Transactions,
        FIND ( Budget[Name], Transactions[Transaction], 1, 0 ) > 0
    )
)

 

v-cherch-msft Super Contributor
Super Contributor

Re: Sum if rows contain matching data

Hi @sfink22

 

SEARCH Function could be worked for you as well.

Column =
SUMX (
    FILTER (
        Transactions,
        SEARCH ( Budget[Name], Transactions[Transaction], 1, 0 ) > 0
    ),
    Transactions[Spend]
)

Regards,

Cherie

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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 50 members 1,067 guests
Please welcome our newest community members: