Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks for the help.
Kind regards
Dominic
Solved! Go to Solution.
@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.
Hi @Anonymous,
Put the value in AXIS in Visualization. Will solve your problem. Thanks
Sorry, this doesn't work.
@Anonymous, you would need to un-pivot the data in the query editor so all of your date columns are in 1 column.
@Anonymous, have a look at this article: http://radacad.com/pivot-and-unpivot-with-power-bi, under the section 'Unpivot'
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.
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |