Forum Discussion
Weighted Average where weights are dynamic based on slicer
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