Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 par...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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
	)
)
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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
	)
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      That worked!! Thank you so much!!!