Forum Discussion
Weighted Average where weights are dynamic based on slicer
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!
5 Replies
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 ZhouHi 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
This is perfect, thank you very much
