Weighted average

[mymun_stat]

Hi, I have a data that look like this:

customer_id	product_id	Weights
1	B	2
2	B	4
2	F	4
2	L	4
2	A	1
2	BE	3
2	K	2
2	LA	5
2	LN	-
2	O	3
2	T	1
3	B	5
3	A	-
3	BE	2
3	F	1
3	K	-
3	L	3
3	LE	5
3	LA	2
3	O	2
3	T	1
4	L	2
4	B	4
4	L	4
4	A	-
4	BE	3
4	F	1
4	K	2
4	LA	3
4	O	4
4	T	2
5	B	4
6	B	5
6	F	5
6	A	3
6	BE	4
6	K	1
6	LA	3
6	LE	2
6	L	3
6	O	1
6	T	2

In the front end table chart, I want to get the weighted average for each product id where weights column is the weight.

So for product A, I should get the weighted average as  (w1x1 + w2x2+ .......... + w5x5)/(x1 + x2 + ......... + x5)

Here,

w1 = 1 AND X1 = count(distinct customer_id) for w1

w2 = 2 AND X2 = count(distinct customer_id) for w2

.

.

.

w5 = 5 AND X5 = count(distinct customer_id) for w5

Thanks in advance.

[adang]

You did not clarify where you want to calculate the weighted average; so I am assuming this is on a chart on the front-end. And here's how you can do it:

1) Add a table object in QS;

2) Add product_id as a dimension; on the measure; you will enter this expression: sum(weight)/count(product_id) and that should give you the weighted average. 

[MarcoWedel]

I think you are describing a regular average of your "weights" field instead of a weighted average.

So  simply "Avg(weights)" as expression should be enough having product_id as dimension.

hope this helps

Marco

[mymun_stat]

Hi, thanks for your reply. Sorry, I did not explain it better before. I have updated the data in the post. The example that you gave is not what I am looking for.

[mymun_stat]

Hi, I have updated the post. Thanks for your reply. sum(weight)/count(product_id) is not what I am looking for.

[MarcoWedel]

maybe like this?:

Sum(Aggr(Count(distinct customer_id)*Weights,Weights,product_id))

hope this helps

Marco

[mymun_stat]

Thanks! It gives the total per product but I have found a way to get weighted average.