Forum Discussion

jamesbr's avatar
jamesbr
Frequent Visitor
9 years ago
Solved

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

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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