Calculating a benchmark using 2 data types, custom states and chart elements


I would like to ask for advice on how to perform a series of calculations using (1) data from two different data types (with a unique identifier = user), (2) custom states, and (3) the Bubble line/bar chart element. The problem is that the chart element can only perform simple aggragations and groupings, and I can’t, for the love of god, figure out how to complement this with custom states.

Context - sample data:

Desired calculation and display in the chart element:

  1. Search for data type Table: (i) constrained by Users in Group 1-10, (ii) grouped by Category
  2. Sum of Spend in each Category group from step 1 / distinct count of occurences of each Category across all Users in Group 1-10 (i.e. average spend by category). To emphasize, the count of occurences should be a distinct count, e.g. for User 1 (Email 1) it should count category A only once although the user has 2 products in the category. This is the issue that prevents me from using the basic chart element as the “average” aggregation cannot do this. It is, however, key for the final calculation.
  3. Plot on a chart to get something like this



Provide an anonymized benchmark view of spend in dif categories for all Users in Group 1-10 :slight_smile: , i.e. a chart that will tell a user in this group “this is how much users like you spend on these categories on average.” This is why the average must be calculated using distinct count.

Will be v grateful for any advice & hope other Bubblers find this useful in the future as well! :slight_smile:

Hello @Thimo !

I did some research and would like to use the Apex Charts plugin here instead. Would you know if it can handle a calculation like this?

Thank you,