Forum Discussion
How to deal with monthly data with multiple attributes
Hi guys,
I have several metrics that are updated monthly, but each metric has several attributes (owner, scope, data provider, etc.) Here's an example of a metric.
Metric: Stations Upgraded
Scope: Budget
Owner: Bill Blocks
Data Provider: Jesse James
| Month | Actual | Goal | Baseline |
| Jan | 15 | 15 | 5 |
| Feb | 18 | 20 | 5 |
| Mar | 24 | 25 | 5 |
| Apr | 31 | 30 | 5 |
I want to plot Actual and Goal and Baseline with Month in the x-Axis and the values in the y-axis. I also need to put all the metrics in one table and show the values. And I need to use slicers to sort by scope, owner, etc.
My problem is I can't find a suitable way to transform the data so I can use it. I initially tried making the metrics be columns and month be rows, but I wasn't able to sort by the other attributes. Then I tried making the month the columns and the metrics the rows, but not every metric has the same units (%, $, dec, etc.) and I couldn't change the data type by row. I decided to make every metric its own table, which works but it has SIGNIFICANTLY slowed down my computer (as I have over 20 metrics to track). Is there any way that I can deal with this data? If I could do something like data type by row, or do a relationship between column and row it would work. I'm open to any solutions and perhaps I have overlooked a very simple one. I really appreciate the help!
Thank you!
You can solve this by using a switch and format function
* In the measures you probably have to change ";" to "," depending on the region you are working in
9 Replies
- parry2k
Super User
Anonymous underline problem is the structure of your data, you need to unpivot it, follow these steps:
- click transform data
- select month column, right-click and select unpivot other columns
- it will add two-column, attribute and value, rename column name as you like
close and apply, pick any visual, let's say line graph
- drop month on x-axis
- drop attribute on legend
- drop value on value section
and you will have what you are looking for.
----
I would 💖 Kudos 🙂 if my solution helped. If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Thank you parry2k ,
I have tried something like this before, but I couldn't it get it to work. Can you clarify what the table should look like before performing the unpivot? Thank you- parry2k
Anonymous the output will look something like this
Month Attribute Value Jan Actual 15 Jan Baseline 15 Jan Goal 5 Feb Actual 18 Feb Baseline 20 Feb Goal 5
WimverhResolver IV
Create a metrics table:
Metric Scope Owner Budget Stations Upgrade Budget Bill Jesse My second metric Budget Donald Jesse Add a column metric to your other table
Date Metric Actual Goal Baseline 1/1/2020 Stations Upgrade 15 15 5 Create a relation 1 to Many between the metric table and your data table.
thanks Wimverh ,
If I do this, will I be able to select the data type for each metric? Metrics are not necesarily the same data type, some are % and others deciamls or currencies.- Wimverh
You can solve this by using a switch and format function
* In the measures you probably have to change ";" to "," depending on the region you are working in
