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 folks!
I have weekly data over multiple years that I'm trying to display in a line chart. The goal is to show one line for each year, with 52 data points intact, but on an X-Axis that only shows the starting point for each month (January-December). Every attempt I've made to put Months in the X-Axis of the chart ends up summarizing/averaging the weekly data points. I can drill up/down, but that's not what I want - would like to see weekly data on a monthly x-axis. Any idea how to make this work? Excel can do it just fine, as shown in the example below:
Solved! Go to Solution.
hi, @AROBERT93
Just turn off Concatenate labels in Formatting->X axis->Concatenate labels
Result:
By the way: Please ensure the chart sort by x axis values.
https://community.powerbi.com/t5/Desktop/concatenate-Labels-does-not-work-properly/td-p/415010
Best Regards,
Lin
hi, @AROBERT93
Just drag Month column into Axis and Year column into Legend.
If not your case, please share a simple sample pbix file for us have a test.
Best Regards,
Lin
Hi Lin!
The data is weekly - putting Month in the X-Axis summarizes the data, which I don't want. I'm looking to preserve the weekly data in the lines, while only showing months on the chart.
This is the summarized view that I want to avoid:
hi, @AROBERT93
Drag WeekNum field into Axis as below, then Expand all and expand one field at a time.
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill
Best Regards,
Lin
Hi Lin!
We are getting much closer! The output now shows month and Week Number, but the Months repeat. Is there any way to group them so it only shows the Month Name once per Month?
Appreciate all the help!
hi, @AROBERT93
Just turn off Concatenate labels in Formatting->X axis->Concatenate labels
Result:
By the way: Please ensure the chart sort by x axis values.
https://community.powerbi.com/t5/Desktop/concatenate-Labels-does-not-work-properly/td-p/415010
Best Regards,
Lin
Hi Lin!
That works! Many thanks for all your help - both X-Axes needed to be numbers (I had been formatting the Month names as text with 'MMM' as the format). I was able to achieve the result by switching back to "Date Hierarchy" for the CalDates and Keeping the Week Numbers as categorical numbers. It looks great!
Hi @AROBERT93 ,
Assuming you are adding Week Ending/Week Starting date in Line Chart, consider changing X axis Type to "Categorical" using Format option of Visual, let me know if it works.
Also, you should look for options like showing last 16-24 weeks in single visual instead of 52 weeks). Adequate data points will make your visual better to understand for end users.
Thank you,
Sandeep
Thanks for the reply, Sandeep!
These are the settings I'm using:
Here is what I get when I set the X-Axis to Categorical (and standardize the weekly dates to all be in the same year - 2019):
What I'm hoping to achieve is the same view, but with only Months on the X-Axis, as in the original post's Excel example.
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |