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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Sum of values in one column based on similar-text values in another column

When using a measure to sum up the Table1[Cost of Purchase]  I get the aggregation of each of the unique company names in Table1[Company]:
-Bob's Bikes
-Bob's Bikes Online

-Bob's Motorcycle and Bicycles

-GlobalCorp

-Dinoco 

 

Table1

CompanyCost of Purchase
Bob's Bikes$50.82
Bob's Bikes Online$97.13
Bob's Bikes Online$12.98
Bob's Bikes$73.72
GlobalCorp$17.26
Bob's Bikes$38.17
Bob's Motorcycle and Bicycles$44.43
GlobalCorp$920.41
Dinoco$51.84

 

Now, I know that all of the Bob's Bikes, Bob's Bikes Online, and Bob's Motorcycle and Bicycles are all from the same company (for the sake of the argument, let's say they've changed the company name a few times over the years) and I want to be able to sum them all up and have them return one aggregated value under Bob's Bikes --I'm also wanting to return the other companies and their respective aggregations as well. I've tried using a FILTER with a LEFT function nested in, but that only gets me Bob's Bikes and the like.

 

An example of what I'm looking to do is listed below.

CompanyCost of Purchase
Dinoco$51.84
GlobalCorp$937.67
Bob's Bikes$317.25



1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

It's more common to do this in Power Query as part of your data prep.  This could range from adding another column with an 'if' statement (if it's simple) OR  creating a separate table with all the corrections (this could be used as dimension table in your model OR creating a separate table and doing a merge with a fuzzy join to find matches (sounds complex but Power Query does all the work).

 

Does that make sense?

View solution in original post

4 REPLIES 4
HotChilli
Super User
Super User

Add a column in Power Query:

if [Company] = "Bob's Bikes Online" then "Bob's Bikes" else if [Company] = "Bob's Motorcycle and Bicycles" then "Bob's Bikes" else [Company]

And here's a confession , I didn't write any of that, I just went in to Add column from examples and let the algorithm work it out.

So for a relatively simple case like this, that's all you need but imagine if there were 20 versions of Bob's Bikes, B'obs Bikes, B's bikes etc, you can then consider the other solutions like   

 if Text.Contains([Company], "ike") then "Bob's Bikes" else [Company]

and again, I didn't write that, I just used the Add Conditional Column.

----

So you use whatever techniques you need to get the job done.

AllisonKennedy
Super User
Super User

@Anonymous  Do you have a common Customer Number or ID for all the Bob's Bikes names? If you have a Customer or Accounts table somewhere that you can export, this would make what @HotChilli  is suggesting much easier... 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

HotChilli
Super User
Super User

It's more common to do this in Power Query as part of your data prep.  This could range from adding another column with an 'if' statement (if it's simple) OR  creating a separate table with all the corrections (this could be used as dimension table in your model OR creating a separate table and doing a merge with a fuzzy join to find matches (sounds complex but Power Query does all the work).

 

Does that make sense?

Anonymous
Not applicable

Those are terrific options, thank you for presenting several for me. I will end up doing the second option because I already know how to do that. For sake of learning, could you elaborate on how I'd use the 'If' condition for another column?

 

Also, thank you for the tip regarding what part of the process I should take care of this, that genuinely helps me learn.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors