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

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

4 Replies

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