I did something like this quite a while back with a lot of help from Stack Overflow. I have tried to adapt it to your case and hopefully it works for you or at least gives you some ideas. Essentially, you can’t create a matrix structure in the Bubble database so I needed to create a pivot table on the fly using javascript in the html element that displays the chart.
Try the following code in a html element and replace the arrays with your searches to make them dynamic (see first screenshot).
Edit: Code with hard-coded values did not work so I have updated to remove the values. Just insert your searches instead as shown in the first screenshot.
<html>
<head>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
// Create and populate the data table.
google.charts.load('current', {'packages':['corechart', 'table']});
google.charts.setOnLoadCallback(drawVisualization);
function drawVisualization() {
var events= [""]; //insert your search for events' event names between the quotes and join with a comma within double quotes. See screenshot
var members = [""]; //insert your search for events' member names between the quotes and join with a comma within double quotes. See screenshot
var sales = []; //insert your search for events' sales between the square brackets and join with a comma. See screenshot
console.log(sales)
var data = new google.visualization.DataTable();
data.addColumn('string', 'Event');
data.addColumn('string', 'Member');
data.addColumn('number', 'Sales');
for(i = 0; i < events.length; i++)
data.addRow([events[i], members[i], sales[i]]);
var distinctValues = data.getDistinctValues(1);
var viewColumns = [0, 1];
var groupColumns = [];
// build column arrays for the view and grouping
for (var i = 0; i < distinctValues.length; i++) {
viewColumns.push({
type: 'number',
label: distinctValues[i],
calc: (function (x) {
return function (dt, row) {
return (dt.getValue(row, 1) == x) ? dt.getValue(row, 2) : 0;
}
})(distinctValues[i])
});
groupColumns.push({
//column: i+2,
column: viewColumns.length - 1,
type: 'number',
//label: distinctValues[i],
aggregation: google.visualization.data.sum
});
}
data.sort({column: 0, desc: false},{column: 1, desc: false});
var view = new google.visualization.DataView(data);
view.setColumns(viewColumns);
// next, we group the view on column A, which gets us the pivoted data
var pivotedData = google.visualization.data.group(view, [0], groupColumns);
// draw agg table
new google.visualization.ChartWrapper({
chartType: 'Table',
containerId: 'table-div',
dataTable: pivotedData
}).draw();
var options = {
vAxis: {title: 'Sales (totals)'},
hAxis: {title: 'Members', maxTextLines: 3},
seriesType: 'bars',
legend: {position: 'top', maxLines: 1, alignment:'center'},
isStacked: 'true'
};
// Create and draw the visualization.
new google.visualization.ComboChart(document.getElementById('curve_chart')).
draw(pivotedData, options);
};
</script>
</head>
<body>
<div id="curve_chart" style="width: 600px; height: 400px" align="center"></div>
<div id="table-div" style="width: 600px; left: 100px" align="center"></div>
</body>
</html>
