Forum Discussion
Help pulling from multiple columns into one in matrix visual
Hi everyone,
I'm trying to pull data from a number of columns into one. The goal is approximately a matrix like this:
Unfortunately, I can't seem to get this right at all. My data looks like this:
This will all be using a month slicer to show current month and let the users look at past months. My issue seems to be I can't say all the actuals are related, along with the plan and variance. The columns just aren't working for me.
1 Reply
What do you mean by your "data looks like this"? Is this the source data? If so, then you need to do the below transformations:
- Unpivot all columns except the first column (month)
- Create a custom column (call it Z for the sake of this exercise) in which you extract the last 3 characters from the "Attribute" column that was created after the unpivotting the columns
- Replace the letters " Act", " Plan", and " Var" of the Attribute column with an empty string
- Now you can display the way you want using a Matrix visual: Attribute field in rows, Z field in columns, and Values field in Values
