Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I have a table like so:
WorkItemID | CycleTimeDays | DaysSinceCompleted |
34 | 2 | -1 |
35 | 2 | -1 |
36 | 6 | -1.25 |
37 | 7 | -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...
Solved! Go to Solution.
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
Proud to be a Super User!
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?
HI @FlowViz,
Perhaps you can take a look at the following link to know more about creating a custom report tooltip page if it is suitable for your requirement.
Create tooltips based on report pages in Power BI Desktop
Regards,
Xiaoxin Sheng
@FlowViz You could create your visual like below
I have create a measure also like below which i have used in tooltip
let me know if it works for you or not. thanks
Proud to be a Super User!
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
Proud to be a Super User!
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
Proud to be a Super User!
@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:
@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
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 😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |