Google Data Studio - Running Weighted Average (based on % of total relative to corresponding data)

Question

I'm trying to create a scorecard to show a 'running' weighted average​​ - that is, depending on the user's selected data from a drop down, it can calculate a weighted average score based on % relative to the corresponding data (i.e. the data selected using the dropdown).

Data Studio (within Tables) allows us to add a comparison calculation with 'Percentage of total * Relative to corresponding data' - which is perfect, since when the user changes the drop down, the comparison calculation is updated based on the corresponding data, and we can see the weight of each row.

However, it doesn't appear to be possible to use the comparison calculation for further metric calculations. To calculate the weighted score, I would need to multiply the Score by the comparison calculation (i.e. the % of Total Orders of the corresponding data) and take the sum of the column.

To give an example (please see spreadsheet for example base data):

Month	Country	# Orders	Score
Apr	FR	1,195	67
Apr	DE	276	63
Apr	CH	788	58
Apr	ES	488	69
May	FR	495	62
May	DE	1,894	44
May	CH	1,496	53
May	ES	1,601	53
Jun	FR	286	71
Jun	DE	275	61
Jun	CH	1,041	69
Jun	ES	1,341	60
Jul	FR	660	64
Jul	DE	1,734	55

Average Score (non weighted) = 58.75

However, if I want to weight the scores based on the # Orders (i.e. the % of orders relative to corresponding data - which, for the purpose of this question, is left as the base data):

Month	Country	# Orders	Score	% of Total Orders (to base data)	Individual Weighted Score
Apr	FR	1,195	67	0.07	4.97
Apr	DE	276	63	0.02	1.08
Apr	CH	788	58	0.05	2.84
Apr	ES	488	69	0.03	2.09
May	FR	495	62	0.03	1.90
May	DE	1,894	44	0.12	5.17
May	CH	1,496	53	0.09	4.92
May	ES	1,601	53	0.10	5.27
Jun	FR	286	71	0.02	1.26
Jun	DE	275	61	0.02	1.04
Jun	CH	1,041	69	0.06	4.46
Jun	ES	1,341	60	0.08	4.99
Jul	FR	660	64	0.04	2.62
Jul	DE	1,734	55	0.11	5.92
Jul	CH	1,267	56	0.08	4.40
Jul	ES	1,276	35	0.08	2.77
				Weighted Average Score	55.71

Weighted Average Score = (Sum of Individual Weighted Scores) = 55.71

Q1 - how do I calculate, or create a column in Data Studio, for the " Individual Weighted Score" - i.e. how can we use the comparison calculation to make a new metric / field and calculate each row's weighted score?

Q2 - how do I display the result, i.e. the 'Running' Weighted Average Score, as a single scorecard? (the user doesn't need to see the full table)

Please see here for the Data Studio example.

Many thanks in advance,

Arran

Answer 1

The expected output fields can be recreated by first adding a fixed SUM(# Orders) field (titled Total Orders below), where values in all rows are the total of the # Orders field. In Google Data Studio, this currently requires reaggregation through a self blend and a cross join:

1) Blend Fields

Data Source:	Table 1	Table 2
Dimension 1:	Month
Dimension 2:	Country
Metric 1: Aggregation: Source Field:	# Orders SUM # Orders	Total Orders SUM # Orders
Metric 1 (Image):
Metric 2: Aggregation:	Score SUM
Date Range:	Month (Auto)	Month (Auto)
Image:

2) Join Configuration

Join Description	Table 1 🔗 Table 2
Join Operator:	Cross
Join Condition:	Cross joins don't require any conditions
Image:

3) Calculated Fields

Description	Field 1	Field 2
Name: Formula: Aggregation: Type:	% of Total Orders (to base data) # Orders / Total Orders SUM Numeric > Percent	Individual Weighted Score (# Orders / Total Orders) * Score SUM Numeric > Number
Image:

Publicly editable Google Data Studio report (embedded Google Sheets data source) to elaborate: