Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have this chart in Excel where I plot dates on the X and hours on the Y axis. Each dot is an event. This helps visualize busy times.
I'm trying to create the same chart in Power BI Desktop, but am failing miserably. The Y axis shows a count of times and the X axis shows a count of dates. I have created an Index column and dragged it to the details, the model shows the correct date format, but I still see only one data point in the middle of the chart. No date hierarchy. The only option is to switch to distinct count.
Is it possible to
Hi @teylyn,
Here is Scatter Plot - Categorical X Axis chart which will help to get categorical variable on X-axis and on Y-axis.
Download link for the custom visual file in this page
https://pbivizedit.com/gallery/scatter-plot-with-categorical-data
This was made with our Custom Visual creator tool PBIVizEdit.com. With this tool,
Give this a shot and let us know if you face any problem/errors.
You can use the editor to modify your visual further (some modifications cannot be done in Power BI window and have to be in editor).
Thanks,
Team PBIVizEdit
Here's a sample workbook implementing that method - the link does not work anymore,
can you provide the new link please?
thanks
Hi, any chance you can show how you managed to do this in Excel? I've been trawling the web to find a way to do this in an Excel chart. So far I've only been able to produce a heatmap pivot table or a Surface chart. When I try to produce a scatter chart I can't get the time to appear on the Y axis.
Unreal that it lacks this simple capability; this is a fairly common use case. Needs a fix.
Hi Teyln,
I have similar problem now.I want simple XY scatter plot with date on x axis and times on Y axis.How can I do this using Power BI.looks like you encounterd similar problem a while back.Appreciate your help.Thanks You.
I got it to work correctly. It is not a huge problem, just that the charting works differently. You can get only numeric values on either axis of a scatter chart, and then split the things you are aggregating in th bubbles using details and legend. The closest you can get is datevalues (or monthvalues, or weeknum) - and you want whatever you use to represent an atomic unit in your dataset (no aggregation). Then it works quite beautifully.
Nopes. Doesn't work with numeric values on both the axis. I have numeric values for weekdays, and numeric values for the hour of the day. Then I want the size of bubble to be the number of vistors. Can't get it. Work perfectly in a matrix layout. But not for the scatter plot. How unintuitive. MS People may have some intelligent reason for this seemingly dumb implementation.
I got it to work correctly. It is not a huge problem, just that the charting works differently. You can get only numeric values on either axis of a scatter chart, and then split the things you are aggregating in th bubbles using details and legend. The closest you can get is datevalues (or monthvalues, or weeknum) - and you want whatever you use to represent an atomic unit in your dataset (no aggregation). Then it works quite beautifully.
I have a similar issue.I want to plot 2 months dates on x axis and time intervals in a span of 24 hours with 15 min intervals break on Y -aixs.So for each day i have 96 Time Stamps.I just need to plot on X and Y with out any aggregation.This is very imp requirement to us.So can anyone pls help.We tried but no luck,everytime trying to drag this field onto visual I am getting the count of Date and Count of time,Which doesnt make anysense.Appreciate quick response and help.
Ive just run into this same problem. Wow. Power BI can't handle dates on scatter plots. In 2016. For info, Tableau handles dates perfectly.
Disclaimer: I don't work for Tableau
I agree it's a big gap, along with not having a Duration data type in DAX (though it exists in Power Query).
Despite their Known Limitations, you can do date-time 'scatterplots' with simple R visuals - e.g. http://stackoverflow.com/questions/7160565/how-to-create-a-time-scatterplot-with-r Though formatting, and converting to POSIXct, can be a bit fiddly.
Here's the code I used to plot Call Times by Weekday for the visual shown below:
library(ggplot2) # Needed for ggplot library(ggthemes) # has a clean theme for ggplot2 library(scales) # Needed for scale_x_datetime #Convert the time format, and account for timezone (so X axis starts at midnight not 08:00!) dataset$`Call Time`<- as.POSIXct(dataset$`Call Time`,format="%Y-%m-%dT%H:%M:%OS", tz="GMT") # Avoid having to reference dataset$ each time in function calls attach(dataset) #Plot the dataset by Week Day (re-ordered from alphabetic) and Call Time ggplot(dataset, aes(x=reorder(`Week Day`,`Week Day Number`), y=`Call Time`)) + #Show points with 90% transparency to cater for overtyping, and ignore NA results in the dataset geom_point(alpha=0.1, size=4, na.rm=TRUE) + #Set the X axis to hourly breaks scale_y_datetime(breaks=date_breaks("2 hours"), labels=date_format("%H:%M")) + #Now some formatting #Apply tidy Tufte theme with no axis ticks theme_tufte(base_family="Arial", ticks=FALSE) + #Clear axis labels and set Title labs(x=NULL, y=NULL, title="Calls by weekday & time of day") + #Rotate X axis theme(axis.text.x = element_text(angle = 90)) + #Format Title theme(plot.title=element_text(hjust=0, color="grey", size=12))
HUGE PROBLEM! PLEASE SORT IT OUT MICROSOFT. Nobody wants a scatter chart that only gives you the option to have a count of values on the X and y axis...
The Scatter charts are designed for measures on x and y axis that is why when you add a text member is will turn this into a count - this happens in all visuals when you put a dimension member into a values field.
Edit: You can use date on the play axis.
seems the issue is that Power BI doesn't like using dates and times on the Axes. I tested the following and it worked.
Use an integer to represent the Day 1, 2, 3 etc
Use a decimal number to represent the time 12.5, 14.75 etc
Add a unique ID column (can be A, B, C or what ever)
Set the default aggreagation for the first 2 columns to be SUM and the third to be DO NOT Aggregate
Put the first 2 on your axes, and the third on details.
Not perfect, but it may get you going. I assume R can do this, but this looks non-trivial to get started (doable, but not trivial).
@MattAllington has nailed it. I tried responding last night, but the forum was eating my replies.
Here's a sample workbook implementing that method (I use dates and an explicit measure rather than a numeric date column, but the effect is the same). This is because the axes of a scatter plot seem to only take a measure, and cannot display literal field values.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
96 | |
90 | |
81 | |
69 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |