Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Structuring Column Data by Dates in a Line Graph

Hi,

 

When data columns are structured like the below, how can I organise them by date in a visual along the X axis? They currently will just sit in the same axis where I would like them to be by month on the X Axis.

 

Capture.PNG

 

 

 

Capture 2.PNG

 

Thanks for the help.

 

Kind regards


Dominic

2 ACCEPTED SOLUTIONS

@Anonymous, have a look at this article: http://radacad.com/pivot-and-unpivot-with-power-bi, under the section 'Unpivot'

View solution in original post

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.

View solution in original post

17 REPLIES 17
zlokesh
Resolver I
Resolver I

Hi @Anonymous,

     Put the value in AXIS  in Visualization. Will solve your problem. Thanks 

 

Anonymous
Not applicable

Sorry, this doesn't work.

jcarville
Skilled Sharer
Skilled Sharer

@Anonymous, you would need to un-pivot the data in the query editor so all of your date columns are in 1 column.

Anonymous
Not applicable

Hi @jcarville, thanks but I am not familair with this?

@Anonymous, have a look at this article: http://radacad.com/pivot-and-unpivot-with-power-bi, under the section 'Unpivot'

Anonymous
Not applicable

Thanks, but is there another way without transposing the data as it will change other visualisations?

You can duplicate the table and un-pivot the data in that table. To do this, you can dupliate the table in the Edit Queries menu.

Anonymous
Not applicable

Thanks, so I have duplicated the table (will this automatically update as the original is refreshed?). Now the visuals look like this, what am I doing wrong?

 

Transposed.PNGVisual.PNG

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

Thanks, I have managed to do this but now the visual is counting the units as opposed to summing and not disinguishing by year?

 

Capture.PNG

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.

Anonymous
Not applicable

Almost there, thank you very much. Although the Sum does not appear?Capture.PNG

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.

Anonymous
Not applicable

Fantastic, you have been amazing. Thank you for all your help, case closed!

Glad to have helped.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.