Scatter Chart - Use Category Label to show bubble size (trying to simulate a Bubble Matrix)

[ReubenPearse]

I've created a Scatter Chart to try and show the number of people engaged in an activity during a month of the year and I've sized the bubble according to the number of people (see Example 1).

However, I would like use the category label show the value of the size of the bubble. Example 2 shows my attempt to do this -  some of the numbers are correct, but some of my data goes missing.

I am also struggling to combine data from 2 years worth of data - I would like the chart to total values for the same month and activity, however it shows 2 seperate bubbles. Any help would be appreciated.

Example 1Example 2

I guess I'm trying to simulate a bubble matrix, but with dynamic filters

https://sites.google.com/site/e90e50fx/home/matrix-bubble-chart-with-excel

[austinsense]

It's really hard to tell what's going on with this. It may be as simple as selecting the drop down on the category field and select "show items with no data".

[ReubenPearse]

I've uploaded a copy of the PBIX file - you can find it here:

Scatter Chart PBIX file

Hopefully you'll be able to see what I'm trying to do.

Thanks

[Sean]

@ReubenPearse

I realize what you are trying to do - plot what I've shown with the Matrix Visualization

BUT you're having trouble because this is not what the Bubble Chart is used for..

[ReubenPearse]

Thanks for putting together these example charts - I can see that this approach will work and achieves a similar output.

Are you able to share the PBIX file you used to create these charts?

[Sean]

@ReubenPearse I used your file - so here's what you do with your file.

First - create a New Page (click the +)

Then add the Participation Year SLICER ( and select 2015)

Now To Create the Matrix - click the Matrix Visualization Button and then drag the fields as in the picture

You may have to Sort the Field Option index (so jist hit the Colomn heading in the Matirx)

Now create a Stacked Column Chart - Hit the Stacked Column Chart Visualization button and

again drag the fiels as in the picture

Then click the Format paint brush - change X-Axis and add data labels

Now make sure you select the Stacked Column Chart we've just created

Hit Ctrl+C then Ctrl+V - move the copy so both chart are visible

Now all you do is select the copy and click the 100% Stacked Column Chart button

If some of the

[ReubenPearse]

Thanks for detailing the steps you took. It's all working well - I just need to get the legend sorted in the same order as the % stacked bars - at the moment it's sorted in the wrong order (see screenshot). Any ideas on how to fix this?

[Anonymous]

Hi @ReubenPearse,

   if you want to sort the legend (just like @Sean tolds you) maybe I have a solution.

You can sort the "FieldOption" by the column "FieldOptionIndex", here what to do:

1) This is the starting point

2) You have to select the "FieldOption" in the "Field Pane", go to "Modelling" and use the Sort by column option.

3) And this is what you'll obtain.

Let me know if it works and if it's what you were searching for.

#I'M Not An Expert#

[Sean]

@ReubenPearse This is very strange it should be sorted that way! Don't know why?

I don't think you can sort the Legend (you can by adding numbers but don't know if its worth it)

This is what I get by default! Try turning it off/on - change the placing? Don't know...