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