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
Anonymous
Not applicable

Sum a column if another contains partial text

I need to sum a column if my quote columns "contains" certain text, it may not be exact because data entry people may put their initials at the end.  For example, I just wrote this, but it's only part of the number because there are a bunch of these quote numbers with things like AJ, AP, BAF or anything else that I can't possibly know.  How do I write "contains" instead of "=".  I've tried * at the end and that results in nothing.  

 

FIIK = CALCULATE(SUM('WorkBookBP'[Net Inv Amt]),FILTER('WorkbookBP','Partial Text Column'[Quote Header.Quote Number]="B1-123456789"))

 

With the above I only get the sum for the one quote number, but there are several more that I need to pick up.

 

Once I get that figured out I need to layer in 4 other numbers with the same issue.

 

Thanks for any help you can give

1 ACCEPTED SOLUTION

If I understand your formula correctly you would put it after the <>0 before the parentheses, then put (I assume) a second FIND() condition there. Something like

 

FIIK = CALCULATE(
	SUM('WorkBookBP'[Net Inv Amt]),
	FILTER(
		'WorkBookBP',
		FIND(
			"P1-123456789",
			'WorkBookBP'[Quote Header.Quote Number],
			,
			0
		) <> 0 ||
		FIND(
			"Q2-123456789",
			'WorkBookBP'[Quote Header.Quote Number],
			,
			0
		) <> 0
	)
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
KHorseman
Community Champion
Community Champion

Other than the alphabetic prefixes, are the numbers after the hyphen a fixed length? Of, for that matter, is it always divided into "something-number"? You could use either the LEFT or RIGHT functions depending on the structure of the number.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

I got this to work for one of the quote numbers, but now I need to add 4 other quote numbers:

 

FIIK = CALCULATE(SUM('WorkBookBP'[Net Inv Amt]),FILTER(''WorkBookBP',FIND("P1-123456789",'WorkBookBP'[Quote Header.Quote Number],,0)<>0))

 

That gave me several lines of results which is perfect.  Now I can't get an OR to work, I've tried || after the last two parenthesis, I've tried leaving out the  ",,0)<>0)) between the II.

 

How do I write OR?

If I understand your formula correctly you would put it after the <>0 before the parentheses, then put (I assume) a second FIND() condition there. Something like

 

FIIK = CALCULATE(
	SUM('WorkBookBP'[Net Inv Amt]),
	FILTER(
		'WorkBookBP',
		FIND(
			"P1-123456789",
			'WorkBookBP'[Quote Header.Quote Number],
			,
			0
		) <> 0 ||
		FIND(
			"Q2-123456789",
			'WorkBookBP'[Quote Header.Quote Number],
			,
			0
		) <> 0
	)
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

That worked!! Thank you so much!!!

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.