Re: Time vs Time Scatter Chart

AsyrafDzul

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!

v-echaithra

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.

AsyrafDzul

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.

stoic-harsh

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

AsyrafDzul

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

stoic-harsh

Power BI aggregates values by default. The fix is to give each record a unique identity. Create a New Column (yeah, you were right🙂) :

// Returns a unique identifier for each record
Concatenated = TEST[Name] & " | OfficeIn: " & TEST[OfficeBadgeIn] & " | SiteIn: " & TEST[SiteBadgeIn]

Replace Staff Name in the (Values field well) with this new column.

Then, for a cleaner look, go to Visualizations pane > Category labels (turn on) > Background (turn on).

Note: Records with identical Name, OfficeIn, and SiteIn values will overlap on the plot, but this shouldn't affect identifying entries outside/inside your target time window. Let me know if it does, and we can come up with a work-around for that. Final result below (4 records of a person Alice; 1 dot is behind the orange one😄. Hope you get it. Since officein and sitein was same for that dot) :

Best,

Harshit

lbendlin

You can use the Deneb visual to work around that limitation

stoic-harsh

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

AsyrafDzul

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! 😀

stoic-harsh

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