Forum Discussion
Structuring Column Data by Dates in a Line Graph
Anonymous, have a look at this article: http://radacad.com/pivot-and-unpivot-with-power-bi, under the section 'Unpivot'
To me that would suggest that the data type for Units Shipped is Text and not Decimal/Whole Number etc. This can be checked by clicking on Units Shipped and under Modeling on the top ribbon there is a data type option.
Correct, it will automatically update as the original is refreshed.
What is wrong now is that your attribute column is a text field, when it needs to be a date field. You will need to remove the word units from each row (again this can be done in edit queries), and the dates Jan 2017, Feb 2017 etc need to be converted to a date e.g. 01/01/2017, 01/02/2017
Thanks, I am unable to change this to a date field due to the text in the columns but is there a way to do this in PowerBi as the original table has Jan 2017 Units and Jan 2017 Value so there would be duplicate value otherwise. If that makes sense?
Yes, all this can be done in the edit queries area of Power BI.
Thanks, I have managed to do this but now the visual is counting the units as opposed to summing and not disinguishing by year?
To distinguish by year, make sure that the date field in your axis has date hierachy turned on (there is a small drop down arrow in the axis field under visualisations for this). This will allow you to drill by year,quarter,month etc.
Then to SUM instead of COUNT, it is similar to above where you access the small drop down arrow, this time in Values and change it from COUNT to SUM.
Almost there, thank you very much. Although the Sum does not appear?
To me that would suggest that the data type for Units Shipped is Text and not Decimal/Whole Number etc. This can be checked by clicking on Units Shipped and under Modeling on the top ribbon there is a data type option.
Fantastic, you have been amazing. Thank you for all your help, case closed!
Glad to have helped.