Bubble Chart and DAX Calculation

[JMSNYC]

Hi.

I cannot figure out the way to build a bubble chart based on this data set, where 

1- x-axis should be the total actual office visits for ANY given customer (i.e. sum column 2)

2- y-axis should be the total planned office visits for ANY given customer (i.e. sum column 3)

3- bubble size is the number of customers that have the same (x,y) combination, e.g., 3 actual, 3 planned

Customer_ID	Monthly_office_visit	Monthly_office_planned
1	1	1
2	1	2
3	2	3
4	2	4
5	3	1
6	3	2
7	1	3
8	2	2
9	2	2
10	2	2
1	3	3
2	3	4
3	1	1
2	1	2
3	2	3
4	2	4
5	3	1
2	3	2
4	1	3

Could anyone please help ?

Thanks

[Sean · Accepted solution]

@JMSNYC

Start by creating these 3 Measures and 1 Column

Visits = SUM ( 'Table'[Monthly_office_visit] )

Planned = SUM ( 'Table'[Monthly_office_planned] )

Size Column =
DIVIDE (
 CALCULATE ( [Visits], ALLEXCEPT ( 'Table', 'Table'[Customer_ID] ) ),
 CALCULATE ( [Planned], ALLEXCEPT ( 'Table', 'Table'[Customer_ID] ) ),
 0
)

Size Measure =
CALCULATE (
 DISTINCTCOUNT ( 'Table'[Customer_ID] ),
 ALLEXCEPT ( 'Table', 'Table'[Size Column] )
)

Then create your bubble chart like this...

Hope this helps! 

[JMSNYC]

Awesome. Works perfectly.

[JMSNYC]

Hi Sean.

Thank you for your quick answer that already helped. I have an incremental question on metrics #3, though. Could you please further help?

Size should be the VOLUME of customers for ANY given pair (planned,visit), i.e. if 41 customers have 2 visits planned and 2 actual vists, then size of bubble should be 41, if 23 customers have 1 visit planned and 3 actual visits, then size of bubble should be 23, etc.

I used 

size = distinctcount(customer_id) ... but when plotted, I got homogeneous bubbles.

What is wrong in my DAX ? 

Thanks