Associate term in column divided by delimiter to a numeric column

[LucasResendeR10]

Hello there! Hope you're all fairing well.

I'm having trouble with a column with terms divided by a delimiter. I want to associate each of the terms with a specific numeric column for that term, so when I sum all the numeric columns, I can use a data slicer to filter specifically for that term, which is my second problem, because I can't put a single term from that column on a data slicer, because they are all together divided by a delimiter. Below follows an example of my problem.
You can see in the table that each term has a column, but I'm not sure how to associate each term to that column, so when I create a visual with the total column, I can use a data slicer to filter that column to show only what the especifi term made. 

Clients name	Terms spent on	Money spent on Term 1	Money spent on Term 2	Money spent on Term 3	Money spent on Term 4	Total
A	Term 1, Term 2	10	10			20
B	Term 3			10		10
C	Term 1, Term 3	10		10		20
D	Term 1, Term 2, Term 3, Tem 4	10	10	10	10	40
E	Term 1, Term 4	10			10	20

[Anonymous]

Hi, @LucasResendeR10 

Thanks for the reply from bhanu_gautam , please allow me to provide addition:

Based on your information, I create a sample table

Then go to the Power Query editor and select the “Terms spent on” column. On the Home tab, click Split Column > By Delimiter. select the delimiter (in your case a comma) and choose Split into Rows.

Close and Apply. Create a new table to create a relationship

Then create meaures:

Total_Term1 = CALCULATE(SUM('Table'[Money spent on Term 1]), FILTER('Table', 'Table'[Terms spent on] = " Term 1"))

Total_Term2 = CALCULATE(SUM('Table'[Money spent on Term 2]), FILTER('Table', 'Table'[Terms spent on] = " Term 2"))

Total_Term3 = CALCULATE(SUM('Table'[Money spent on Term 3]), FILTER('Table', 'Table'[Terms spent on] = " Term 3"))

Total_Term4 = CALCULATE(SUM('Table'[Money spent on Term 4]), FILTER('Table', 'Table'[Terms spent on] = " Term 4"))

Create a slicer view and table view. Put measures and fields in Table view. Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LucasResendeR10]

Hello @Anonymous, first of all, thank you for the reply! Unfortunatly each row is a different process from different clients, so if I divide by rows the number of processes registered would increase, so I can't do that. 

[bhanu_gautam · Accepted solution]

@LucasResendeR10 , Try below method

Step 1: Split the Terms Column
Go to Power Query Editor.
Select the "Terms spent on" column.
Use the "Split Column" feature by delimiter (comma in this case).
Step 2: Unpivot the Data
Select the columns "Money spent on Term 1", "Money spent on Term 2", "Money spent on Term 3", and "Money spent on Term 4".
Use the "Unpivot Columns" feature.
Step 3: Create Relationships
Create a new table with unique terms (Term 1, Term 2, Term 3, Term 4).
Create a relationship between the unpivoted data and this new table based on the terms.
Step 4: Use a Slicer
Add the unique terms table to your report.
Create a slicer visual using the terms from the unique terms table.