Forum Discussion
Line chart using a table that has multiple fields for x axis
Ya, the "shape" of your data is not helping you. In the query update window thingy (power query) -- check out the UNPIVOT feature, that would change your life.
Hi megavic,
Based on your description, it seems that you want to display multiple fields’ values in different years and different months using Line chart. In Line chart, when you put Month into X-axis and put Year into Legend, you are only able to drag one field into Values. The visual will look like as follows.
Moreover, in Line chart, when you put Month into X-axis and drag multiple fields into Values, you are not able to drag Year into Legend, please check the following example for more details.
In your scenario, Matrix visual is more appropriate.
Thanks,
Lydia Zhang
Hi Lydia,
Many thanks for your speedy response!
I understand everything you've said, and I am already showing the data in a matrix - I did however want to show the data at the same time in a simple line graph. I'm thinking I could perhaps pull all the data in to a single field in access. Is there anything I could do in power BI query to move the data in to a single field?
Hi megavic,
You can consider to create a calculated column to sum the values of these fields, then create Line chart by putting Month into X-axis and dragging the new column into Values and dragging Year into Legend.
For example: column = Test[Period1]+Test[Period2]+Test[Period3]+Test[Period4]+Test[Period5]+Test[Period6]+Test[Period7]
Thanks,
Lydia ZhangThanks Anonymous,
I think the issue with I have with that idea is that I don't have a 'month' field.
I'll have a look at what I can do to manipulate the data before it gets in to Power BI.
Thanks again,
Andy
Ya, the "shape" of your data is not helping you. In the query update window thingy (power query) -- check out the UNPIVOT feature, that would change your life.