Controlling the bubble size in a scatter chart

[jamesbr]

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

[v-yulgu-msft · Accepted solution]

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