Multi-Color Bubble chart

[sm19]

Hello,

I am an experienced BI developer but new to Power Gadgets and am loving the trial.  I have had success with everything so far but the multi-color bubble chart.  How should I structure my query if I want the following display:

x-axis: age

y-axis: success percentage

size of bubble: salary

color of bubble: role 

 Any help provided is much appreciated!

SM 

[sm19]

I wanted to follow-up with my post to give an example.  I would like to color the bubbles by groups similar to this Trendalyzer display

http://www.gapminder.org/world/

 Is this possible?SM

[JuanC]

If you are using the PowerGadgets creator, you will be able to plot Age, Success Percentage and Salary as you need but not the coloring depending on the region. A bubble chart requires 2 series, the first one will be used for the Position of the bubble and the second one for the size of the bubble. Additionally, it supports X values so your query should return Age,SucessPerc,Salary.

If you are willing to create the gadget using a powershell script, you can use a feature implemented in a later build that allows you to write a little function that controls which conditional attribute is applied to a point. You can read more about it here.

Contents of BubbleData.csv
-----------------------------------------------------
Age,SuccessPerc,Salary,Role
22,45,1500,R0
25,75,2500,R1
35,53,1100,R0
42,62,1300,R2
-----------------------------------------------------

Contents of Bubble.ps1
-----------------------------------------------------
$condition = {
  if ($_.Role -eq "R0") {
  0;
  } else {
  if ($_.Role -eq "R1") {
  1;
  } else {
  2;
  }
  }
}

import-csv C:\Data\BubbleData.csv | out-chart -gallery Bubble -values SuccessPerc,Salary -xvalues Age -axisY_Title_Text "Success Percentage" -axisx_Title_Text "Age" -axisy2_Visible false -axisX_Min 10 -axisX_Max 50 -axisy2_Min 0 -axisy2_Max 4000 -conditionalattributes_0_color red -conditionalattributes_1_color yellow -conditionalattributes_2_color green -condition $condition -conditionalattributes_0_text "Role 0" -conditionalattributes_1_text "Role 1" -conditionalattributes_2_text "Role 2" -legendbox_itemattributes_-series_visible false

Note the following

• The way our axes min and max are calculated results sometimes in clipping of bubbles, for this chart I am setting the Min/Max for both the X axis and secondary Y axis used for the size of the bubbles
• We define as many conditional attributes as roles
• We assign a condition that returns a zero-based integer depending on the role field
• If you assign a text to your conditions they will appear in the legend box
• The very last parameter hides the series items from the legend box, it is a weird syntax but it works

Once you have your chart running you can use a trick where with the chart gadget with the focus you press Ctrl-C, this will copy the chart's definition to the clipboard, you can then run the PG creator and pressing Ctrl-V should add a chart gadget with all the attributes. Note that this gadget will use Powershell as its data source. If you need to use data from a database you can use invoke-sql instead of import-csv

Attachment shows the resulting chart.

Regards,

JuanC

 

[sm19]

Juan,

 Thank you very much for your detailed reply.  Ihave adapted your method and its working nicely, thanks!  Is itpossible to apply a name on hover to each bubble?  My requirement issimilar to the Trendalyzer data in that color defines continent but Iwould still like to hover over the bubble to see the country name. Possible?

 Thanks,

SM

[marco.shaw]

Contents of Bubble.ps1

-----------------------------------------------------

$condition = {

  if ($_.Role -eq "R0") {

  0;

  } else {

  if ($_.Role -eq "R1") {

  1;

  } else {

  2;

  }

  }

}

import-csv C:\Data\BubbleData.csv | out-chart -gallery Bubble -values SuccessPerc,Salary -xvalues Age -axisY_Title_Text "Success Percentage" -axisx_Title_Text "Age" -axisy2_Visible false -axisX_Min 10 -axisX_Max 50 -axisy2_Min 0 -axisy2_Max 4000 -conditionalattributes_0_color red -conditionalattributes_1_color yellow -conditionalattributes_2_color green -condition $condition -conditionalattributes_0_text "Role 0" -conditionalattributes_1_text "Role 1" -conditionalattributes_2_text "Role 2" -legendbox_itemattributes_-series_visible false

NOTE: I tried this with versions 1.0.2806.25474 and 1.0.2855.20057.  My entire PowerShell session dies...  I'm not quite sure why just yet.  Could be an issue with using copy and paste of the code.

[sm19]

Thanks again for your replies.  One more question, is itpossible to apply a name on hover to each bubble?  My requirement issimilar to the Trendalyzer data in that color defines continent but Iwould still like to hover over the bubble to see the country name. Possible?

Thanks,

SM