Forum Discussion
Weighted Average where weights are dynamic based on slicer
Posting data as tables for ease instead of pictures
| SalesID | Region | Country/State | Product Type | SalesPremium |
| 1 | US | New York | A | 200 |
| 2 | US | LA | A | 300 |
| 3 | US | New York | A | 400 |
| 4 | US | LA | B | 500 |
| 5 | US | LA | B | 600 |
| 6 | US | LA | B | 700 |
| 7 | US | LA | B | 800 |
| 8 | US | Boston | B | 900 |
| 9 | US | Boston | B | 1000 |
| 10 | US | New York | D | 1100 |
| 11 | US | New York | D | 1200 |
| 12 | US | Boston | D | 1300 |
| 13 | Europe | UK | B | 1400 |
| 14 | Europe | UK | B | 1500 |
| 15 | Europe | UK | B | 1600 |
| 16 | Europe | France | B | 1700 |
| 17 | Europe | France | C | 1800 |
| 18 | Europe | France | C | 1900 |
| 19 | Europe | France | C | 2000 |
| 20 | Europe | Germany | C | 2100 |
| 21 | Europe | Germany | D | 2200 |
| 22 | Europe | Germany | D | 2300 |
| 23 | Europe | UK | D | 2400 |
| 24 | Europe | UK | D | 2500 |
| 25 | Europe | Spain | A | 2600 |
| 26 | Europe | Spain | A | 2700 |
| 27 | Europe | Spain | A | 2800 |
| 28 | Europe | Spain | B | 2900 |
| 29 | Europe | Spain | B | 3000 |
| 30 | Europe | France | B | 3100 |
| 31 | Europe | France | B | 3200 |
| 32 | Europe | Germany | C | 3300 |
| 33 | Europe | Germany | C | 3400 |
| 34 | Europe | Germany | C | 3500 |
| 35 | Europe | Germany | C | 3600 |
| 36 | Europe | Germany | D | 3700 |
| 37 | Europe | UK | D | 3800 |
| 38 | Europe | UK | D | 3900 |
| 39 | Europe | Uk | D | 4000 |
Country/State Development Table
| Country/State | Quarter | Development |
| New York | 1 | 0.05 |
| New York | 2 | 0.1 |
| New York | 3 | 0.15 |
| New York | 4 | 0.2 |
| New York | 5 | 0.25 |
| New York | 6 | 0.3 |
| New York | 7 | 0.35 |
| New York | 8 | 0.4 |
| New York | 9 | 0.45 |
| New York | 10 | 0.5 |
| Boston | 1 | 0.02 |
| Boston | 2 | 0.045 |
| Boston | 3 | 0.07 |
| Boston | 4 | 0.095 |
| Boston | 5 | 0.12 |
| Boston | 6 | 0.145 |
| Boston | 7 | 0.17 |
| Boston | 8 | 0.195 |
| Boston | 9 | 0.22 |
| Boston | 10 | 0.245 |
| LA | 1 | 0.1 |
| LA | 2 | 0.2 |
| LA | 3 | 0.3 |
| LA | 4 | 0.4 |
| LA | 5 | 0.5 |
| LA | 6 | 0.6 |
| LA | 7 | 0.7 |
| LA | 8 | 0.8 |
| LA | 9 | 0.9 |
| LA | 10 | 1 |
| France | 1 | 0.07 |
| France | 2 | 0.14 |
| France | 3 | 0.21 |
| France | 4 | 0.28 |
| France | 5 | 0.35 |
| France | 6 | 0.42 |
| France | 7 | 0.49 |
| France | 8 | 0.56 |
| France | 9 | 0.63 |
| France | 10 | 0.7 |
| UK | 1 | 0.05 |
| UK | 2 | 0.1 |
| UK | 3 | 0.15 |
| UK | 4 | 0.2 |
| UK | 5 | 0.25 |
| UK | 6 | 0.3 |
| UK | 7 | 0.35 |
| UK | 8 | 0.4 |
| UK | 9 | 0.45 |
| UK | 10 | 0.5 |
| Germany | 1 | 0.07 |
| Germany | 2 | 0.14 |
| Germany | 3 | 0.21 |
| Germany | 4 | 0.28 |
| Germany | 5 | 0.35 |
| Germany | 6 | 0.42 |
| Germany | 7 | 0.49 |
| Germany | 8 | 0.56 |
| Germany | 9 | 0.63 |
| Germany | 10 | 0.7 |
| Spain | 1 | 0.02 |
| Spain | 2 | 0.045 |
| Spain | 3 | 0.07 |
| Spain | 4 | 0.095 |
| Spain | 5 | 0.12 |
| Spain | 6 | 0.145 |
| Spain | 7 | 0.17 |
| Spain | 8 | 0.195 |
| Spain | 9 | 0.22 |
| Spain | 10 | 0.245 |
Product Type Development Table
| Product Type | Quarter | Development |
| A | 1 | 0.05 |
| A | 2 | 0.1 |
| A | 3 | 0.15 |
| A | 4 | 0.2 |
| A | 5 | 0.25 |
| A | 6 | 0.3 |
| A | 7 | 0.35 |
| A | 8 | 0.4 |
| A | 9 | 0.45 |
| A | 10 | 0.5 |
| B | 1 | 0.02 |
| B | 2 | 0.045 |
| B | 3 | 0.07 |
| B | 4 | 0.095 |
| B | 5 | 0.12 |
| B | 6 | 0.145 |
| B | 7 | 0.17 |
| B | 8 | 0.195 |
| B | 9 | 0.22 |
| B | 10 | 0.245 |
| C | 1 | 0.1 |
| C | 2 | 0.2 |
| C | 3 | 0.3 |
| C | 4 | 0.4 |
| C | 5 | 0.5 |
| C | 6 | 0.6 |
| C | 7 | 0.7 |
| C | 8 | 0.8 |
| C | 9 | 0.9 |
| C | 10 | 1 |
| D | 1 | 0.07 |
| D | 2 | 0.14 |
| D | 3 | 0.21 |
| D | 4 | 0.28 |
| D | 5 | 0.35 |
| D | 6 | 0.42 |
| D | 7 | 0.49 |
| D | 8 | 0.56 |
| D | 9 | 0.63 |
| D | 10 | 0.7 |
Hi katbro20 ,
How is the weighted average calculated?' Is 'Development' your weight column?
Like this?
If that is the case, this is the result I obtained
Product =
VAR _quarter = SELECTEDVALUE('Quarter'[Value])
VAR _table = ADDCOLUMNS(SUMMARIZE('SalesData',[Product Type],"Sum",SUM(SalesData[SalesPremium])),"result",VAR _type = [Product Type] RETURN MAXX(FILTER('Product Type Development Table',[Product Type] = _type && [Quarter] = _quarter),[Development]) * [Sum])
VAR _a = SUMX(_table,[result])
VAR _b = CALCULATE(SUM('Product Type Development Table'[Development]),USERELATIONSHIP('Quarter'[Value],'Product Type Development Table'[Quarter]))
RETURN DIVIDE(_a,_b)
For quarter1 :
1928.57 = (900 * 0.05) + (4500 * 0.02) / (0.05 + 0.02)
If I have misunderstood, please let me know the steps of the calculation and the expected results
Best Regards,
Wenbin Zhou
Hi Anonymous ,
No the developments are the things that I want weighted. Ie I would like a total development column by quarter, weighted by the premium (in the sales data) for each product type. (and for each country/state seperately if selected)
So very simply, if, for example, the slicer were to select product type A and B, I would like the weight for A in the Product Type Development Table to be the total premium for A (in the selected region) divided by the total premium for both product type A and B (in the selected region). So for example, if the US was selected:
These weights would then be applied to every value in the development column for product type A and B respectively.
The final result would then be the sum of the weighted development column for each quarter.
Hopefully this is clearer, but let me know if not, thanks