I am looking for some help in converting some of the columns in my data to values in a dimension. Please find attached a sample workbook with what I have so far. I have also added the original data to the workbook for reference.
If you click on the original data in the dashboard, you can see 16 dimensions that have their names as *_Goal, *_Actual, *_Status or *_Bench. I need the * in these dimension names to be values of a new column, which I am able to get by using the Pivot option and a calculated field - 'Metric Name'. I created 4 new calculated fields (Goal, Actual, Bench and Status) to map the correct pivot values but something seems to be wrong as I am not getting the correct values in a cross-tab. You can see on Sheet 2 that 'Goal' has an extra 'Null' value and all the values of the field 'Actual' map to this Null value. So, excluding the Null doesn't help. What I have on Sheet 1 shows correct values when I hover over the green shapes, but that is not the final representation I am looking at.
My sheet should finally look like below - an example row added. It's probably something simple I'm missing here. Please help.