Forum Discussion

FlowViz's avatar
FlowViz
Helper III
5 years ago
Solved

Scatter Chart Bubble Size

Hi Guys,

 

I have a table like so:

 

WorkItemID  CycleTimeDays DaysSinceCompleted
342-1
352-1
366-1.25
377-2

 

Which is visualised in a scatter chart (CycleTimeDays - Y-Axis / DaysSinceCompleted - X-Axis).

Unfortunately the bubbles overlap for certain ID's (see WorkItemID 34 and 35). 

Is there a way to create a measure for the bubble size? I'd like to highlight overlapping items with a larger bubble...

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi FlowViz 

     

    Download PBIX with these examples

     

    Yes you can use this measure for bubble size

     

    Bubble Size = CALCULATE(COUNTROWS('Table'), FILTER(ALL('Table'), 'Table'[CycleTimedays] = SELECTEDVALUE('Table'[CycleTimedays]) && 'Table'[DaysSinceCompleted] = SELECTEDVALUE('Table'[DaysSinceCompleted])))

     

     

    To give this

     

    You might also consider introducing jitter to one of the axes to shift overlapping points slightly.

    This measure shifts the x value slightly for points that overlap.  It uses the [Bubble Size] measure from above.

     

    Jittered X = IF([Bubble Size] > 1, SELECTEDVALUE('Table'[DaysSinceCompleted])*(RAND()), SELECTEDVALUE('Table'[DaysSinceCompleted]))

     

     

    Giving this visual.  The amount of jitter/shift for each point can be tweaked so it's not shifted too much.

     

    Further info on jitter Jitter in Excel Scatter Charts • My Online Training Hub

     

    Regards

    Phil

9 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi FlowViz 

     

    Download PBIX with these examples

     

    Yes you can use this measure for bubble size

     

    Bubble Size = CALCULATE(COUNTROWS('Table'), FILTER(ALL('Table'), 'Table'[CycleTimedays] = SELECTEDVALUE('Table'[CycleTimedays]) && 'Table'[DaysSinceCompleted] = SELECTEDVALUE('Table'[DaysSinceCompleted])))

     

     

    To give this

     

    You might also consider introducing jitter to one of the axes to shift overlapping points slightly.

    This measure shifts the x value slightly for points that overlap.  It uses the [Bubble Size] measure from above.

     

    Jittered X = IF([Bubble Size] > 1, SELECTEDVALUE('Table'[DaysSinceCompleted])*(RAND()), SELECTEDVALUE('Table'[DaysSinceCompleted]))

     

     

    Giving this visual.  The amount of jitter/shift for each point can be tweaked so it's not shifted too much.

     

    Further info on jitter Jitter in Excel Scatter Charts • My Online Training Hub

     

    Regards

    Phil

    • FlowViz's avatar
      FlowViz
      Helper III
      5 years ago

      Thanks Phil - could I use jittering to avoid having the need for a larger bubble size? If so how would I do that?

      • FlowViz's avatar
        FlowViz
        Helper III
        5 years ago

        Ah I actually realised that over time (as DaysSinceCompleted increases) the jitter won't work as the new measure will give a range of results since it was completed 😞

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      PhilipTreacy Thanks for a great solution, I had the same issue. 

      However, my x & y values only range between 1-5, is it possible to use a lower value for the shift? As far as I know RANDBETWEEN doesn't allow for decimals? Any other ideas?

      I want as small shift as possible, but still enough so it's noticable

    • jackmeull's avatar
      jackmeull
      New Member
      3 years ago

      PhilipTreacy I know this is an old thread, but I have a similar issue and would like to make a bubble size measure just like this except with field parameters allowing the user to select x and y axis variables. My code is:

       

      Bubble Size = CALCULATE(COUNTROWS('A1'), FILTER(ALL('A1'), 'X Question'[X Question Fields] = SELECTEDVALUE('X Question'[X Question Fields]) && 'Y Question'[Y Question Fields] = SELECTEDVALUE('Y Question'[Y Question Fields])))
       
      and I get an error saying a single value cannot be determined. How can I refer to my parameters in terms of table A1?
       
      Thanks!
  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi FlowViz 

    Yes, using jitter for overlapping ponts will avoid the need for larger bubbles.

    I'm not sure what your subsequent comment means : over time (as DaysSinceCompleted increases) the jitter won't work as the new measure will give a range of results since it was completed ?

    If you aren't going to or can't use jitter, will you just use the larger bubble solution?

    Regards

    Phil

  • negi007's avatar
    negi007
    Community Champion
    5 years ago

    FlowViz You could create your visual like below

     

     

    I have create a measure also like below which i have used in tooltip

    Items = if ( COUNT(WorkItemID[ CycleTimeDays]) >1, "Multiple Items", "Single Item")
     
     

    let me know if it works for you or not. thanks

  • FlowViz's avatar
    FlowViz
    Helper III
    5 years ago

    I think the larger bubble solution works better. DaysSinceCompleted is calculated using NOW()-Closed Date. So over time this will increase and using jitter could skew how many days since it was actually completed.

     

    With the larger bubble, is it possible to do a custom tooltip that would show the data for each item on hover?