Forum Discussion
Associate term in column divided by delimiter to a numeric column
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 |
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.
3 Replies
- bhanu_gautam
Super User
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. 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.
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.
