Count Number of Matches in all rows

[OSin]

Hi All,

I have searched for a solution to this question but have had no luck so far.

I would like to calculate the number of string occurences in all rows of a certain column, based on a string in the current row of a seperate column. For example:

BOM

COMPONENT2,COMPONENT3,COMPONENT3

COMPONENT1,COMPONENT5,COMPONENT3

COMPONENT2,COMPONENT1

COMPONENT1,COMPONENT3

COMPONENT1,COMPOMENT2,COMPONENET3,COMPONENT4

SKU	SKU in BOM
COMPONENT 1	4
COMPONENT 2	3
COMPONENT 3	5
COMPONENT 4	1
COMPONENT 5	1

Currently, I can count the rows where the string occurs using DAX:

SKU in BOM = countrows(filter('Data',find(earlier('Data'[SKU]),'Data'[BOM],,0)>0))

But this gives just a count of the rows, wheras I want to get the total number of occurances.

I have seen a suggestion of using Text.Split to get the number of occurences, but I'm not sure how to reference the "SKU" Column as the delimiter and how to iterate over every row and sum, rather than just the current row.

Edit: The main issue I have is that each component can be present more than once in any given row. Editted the tables to clarify.

Any help would be appreciated.