Forum Discussion
Time vs Time Scatter Chart
Hi All,
I've been trying to recreate a scatter chart i made in excel in the powerBI. I've read it somewhere that the x & y-axis needs to be a decimal value in order to create a scatter chart. That's another problem solved. But how do i change the display data into time again instead of decimal point? I've tried looking into the visual format, but nothing came up. Appreciate y'all insights regarding this issue.
Thanks in advance!
Hey AsyrafDzul,
Here is a neat trick! Since scatter plots require numeric axes, we convert time values into HH.MM decimal format - readable as time (dimension), plottable as a number (measure). Example:
// Calculated Column OfficeBadgeIn_HHMM = VAR Hours = HOUR('TEST'[OfficeBadgeIn]) VAR Minutes = MINUTE('TEST'[OfficeBadgeIn]) RETURN Hours + ( Minutes / 100 )The HH.MM format is numeric enough for Power BI to plot, and intuitive enough to read as time (use appropriate call-outs for business users).
Hope it helps! Please do share if you find other workarounds.
Best,
Harshit
Hi AsyrafDzul ,
Thank you stoic-harsh for your valuable innputs.
Try this:Click dropdown on X Axis field
Set aggregation = Average or Sum
Same for Y Axis
Do NOT choose "Don't summarize"
If you still see grouped points after adding Staff Name to Values, create a unique row ID:
RowID = 'Table'[Staff Name] & "-" & FORMAT('Table'[Date],"yyyymmdd")
Then use RowID in Values instead of Staff Name.
Also keep your time decimal columns as Calculated Columns, not Measures. That will give you one dot per row/record instead of aggregated totals.
Hope this helps.
12 Replies
Hey AsyrafDzul,
Here is a neat trick! Since scatter plots require numeric axes, we convert time values into HH.MM decimal format - readable as time (dimension), plottable as a number (measure). Example:
// Calculated Column OfficeBadgeIn_HHMM = VAR Hours = HOUR('TEST'[OfficeBadgeIn]) VAR Minutes = MINUTE('TEST'[OfficeBadgeIn]) RETURN Hours + ( Minutes / 100 )The HH.MM format is numeric enough for Power BI to plot, and intuitive enough to read as time (use appropriate call-outs for business users).
Hope it helps! Please do share if you find other workarounds.
Best,
Harshit
I did it guys!
Upon a little bit of exploration and the help from y'all. I managed to recreate exactly what I did in the excel file. Again, thank you very much!
Nice. The visuals look stunning.
Best,
Harshit
Hi Mr. stoic-harsh ,
May I know whether the formula should be created in Excel before importing it as a database, or can it be written directly in ‘Transform Data’? I’m new to this, thank you very much for your time! 😀Hello Sir 😆,
Simply load your data without any transformation into the model (I assume, your table contains OfficeBadgeIn and SiteBadgeIn values in time format). Then, use the DAX I provided in previous reply, to create 2 columns in HH.MM decimal format (don't use New Measure here). Let me know in case of any questions.
Best,
Harshit
You can use the Deneb visual to work around that limitation
Hi lbendlin ,
I did some walkaround with Deneb Visual and I find the code was easy to understand. But since I am very new with it-Deneb Visual, it might require me some time to recreate the visual that I've done in excel. I'll consider this method too in the near future!
Thank you very much.
Hi Mr. stoic-harsh ,
Everything works perfectly! I am sorry if this sounded like I am asking for a step-by-step tutorial. How do I change the axes display into a time format? I tried to work around the "format visual" but nothing came out to change the value.
No worries. I am here to help anyways.
Click this icon in Visualizations pane, then go to Visual > X-axis > Values > (set display units to None) > Adjust Decimal Place to 2 D.P
Repeat for Y-axis. And done!
Best,
Harshit
Hi stoic-harsh ,
Thanks, it works! Now I'm thinking to display these dots in an individual value instead of sum so that I can see the actual value when I enable the Within Window/After 0930/Before 0830 filter.
But when I clicked "Don't Summarise", the display shows an error as per snapshot below.
I did some search on how to solve this, but none came up (again). Do I have to setup another DAX code for this?Sorry for the hard time,
Asyraf
Hi AsyrafDzul ,
Thank you stoic-harsh for your valuable innputs.
Try this:Click dropdown on X Axis field
Set aggregation = Average or Sum
Same for Y Axis
Do NOT choose "Don't summarize"
If you still see grouped points after adding Staff Name to Values, create a unique row ID:
RowID = 'Table'[Staff Name] & "-" & FORMAT('Table'[Date],"yyyymmdd")
Then use RowID in Values instead of Staff Name.
Also keep your time decimal columns as Calculated Columns, not Measures. That will give you one dot per row/record instead of aggregated totals.
Hope this helps.
