Weighted Average where weights are dynamic based on slicer

[katbro20]

Hello,

I'm quite new to power BI and so hopefully I've posted this in the correct place. I'm trying to get the weighted average of a quarterly development % list weighted by sales premium, and I want the weights to be dynamic based based on what is selected in the slicer.

My sales data looks like this (this is not complete), and so there is a region, country/state, and product type associated with every sale premium.

There are then two seperate tables which contain quarterly development list (again not complete) for every product type,

 

and for every country/state.

What I would like is to get a weighted average of the development pattern based on the sales premium (at the moment then development patterns are just showing simple average). The bit that I'm really struggling with is that I would like the ability to select multiple values from the slicers (eg select france and germany, and then have the weighted average of the  country/state development patterns just based on the premiums from those two countries).

I would then do this seperately for the other product type development pattern table based on the product type slicer (again with the ability to base the weights % on multiple selected values)

The last part would be to show the weighted average of both types of development patterns (the country/state and the product type) on the same graph, but I think if I have my relationships correct, that should be easy.

This is a simplified version of what I'm trying to do, and so if explanations are needed then I will definately put some more color around the problem, just send post asking what I am missing!

Thank you very much!

[katbro20]

This is perfect, thank you very much

[katbro20]

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

[Anonymous]

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

[katbro20]

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

[Anonymous · Accepted solution]

Hi @katbro20 ,

Best Regards,
Wenbin Zhou