cancel
Showing results for
Did you mean:
Frequent Visitor

## Controlling the bubble size in a scatter chart

I cannot get the bubble size to work as required...  I have a table that contains the sales of a team:

James | 16

James | 15

James | 14

Bob | 15

Bob | 13

Janet | 20

That can draw me a scatter chart that shows the number of deals and the value of those deals.  Perfect.

I then want to create the bubble size based on their current close rate

James | .25

Bob | .5

Janet | .8

So i join the two tables to create a relationship, and then pull the 'CloseRate' to create the bubble size.  Therefore showing an indication of their ability to close deals.

However I am forced to select either a Sum, Average etc for the CloseRate and all the values in the chart are the sales.  So for example if I select 'Sum of CloseRate' every bubble is the same size and has an value of 1.55 (eg .25+.5+.8).

How can I bring the data into the scatter chart so that I get the correct values assigned to the correct dots?

Thanks

James

1 ACCEPTED SOLUTION
Microsoft

Hi @jamesbr,

By default, if we drag a column into "Size" section in a scatter chart, it will be aggregated, like Sum, Average, Count, etc. To avoid this beavior, we can add a measure insetad of adding column directly. Below is my test, please review it.

Add a calculated column [Bubble Size] in sales table.

```Bubble Size =
LOOKUPVALUE (
'Bubble Size'[Close Rate],
'Bubble Size'[Name], 'Sales of team'[Name]
)```

Create a measure.

```Bubble Size measure =
CALCULATE (
LASTNONBLANK ( 'Sales of team'[Bubble Size], 1 ),
ALLEXCEPT ( 'Sales of team', 'Sales of team'[Name] )
)```

Add this measure into "Size" section.

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft

Hi @jamesbr,

By default, if we drag a column into "Size" section in a scatter chart, it will be aggregated, like Sum, Average, Count, etc. To avoid this beavior, we can add a measure insetad of adding column directly. Below is my test, please review it.

Add a calculated column [Bubble Size] in sales table.

```Bubble Size =
LOOKUPVALUE (
'Bubble Size'[Close Rate],
'Bubble Size'[Name], 'Sales of team'[Name]
)```

Create a measure.

```Bubble Size measure =
CALCULATE (
LASTNONBLANK ( 'Sales of team'[Bubble Size], 1 ),
ALLEXCEPT ( 'Sales of team', 'Sales of team'[Name] )
)```

Add this measure into "Size" section.

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.