Forum Discussion
Scatter Chart Bubble Size
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
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
Thanks Phil - could I use jittering to avoid having the need for a larger bubble size? If so how would I do that?
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 😞
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
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!
