Weighted Average in time spread...

[#1]

Dear users,

I want to show weighted average on a Line Chart. Dimensions are: YearMonth and Category.

Below example regarding one project, there will be many YearMonth records, but Category will be still 1, 2, 3...

At the moment, my weighted average metric is like below, but it calculates me total by number of months. Is it possible to show me aggregated weighted average for each YearMont dimension? This will allow to show me weighted averages on linear chart, so there will be possibility to track weighted average changes in time.

Thank you in advance.

//Weighted average metric:
sum (
    aggr(
        (sum(volume) / sum(total volume))*
          sum(nettprice), category, YearMonth)    )

YearMonth	Category	NettPrice	Weighted Avg	Volume	Desired goal of showing weithted avg on line chart
	Category1		216	1 263,34
2016-06	Category1	216,00	30,86	180,48	216,00
2016-07	Category1	216,00	30,86	180,48	216,00
2016-08	Category1	216,00	30,86	180,48	216,00
2016-09	Category1	216,00	30,86	180,48	216,00
2016-10	Category1	216,00	30,86	180,48	216,00
2016-11	Category1	216,00	30,86	180,48	216,00
2016-12	Category1	216,00	30,86	180,48	216,00
	Category2		115,2	134,55
2016-06	Category2	115,20	16,46	19,22	115,20
2016-07	Category2	115,20	16,46	19,22	115,20
2016-08	Category2	115,20	16,46	19,22	115,20
2016-09	Category2	115,20	16,46	19,22	115,20
2016-10	Category2	115,20	16,46	19,22	115,20
2016-11	Category2	115,20	16,46	19,22	115,20
2016-12	Category2	115,20	16,46	19,22	115,20

[marcus_sommer]

It's not quite clear for me how do you want to calculate the average but maybe you need a further aggr() for it, maybe something like this:

avg(aggr(

     sum (aggr(

          (sum(volume) / sum(total volume))* sum(nettprice), category)),

     YearMonth))

Also this might be helpful for you: Average – Which average?

- Marcus

[sunny_talwar]

Not sure, but may be this:

Sum(TOTAL <Category> Aggr( (Sum(volume)/Sum(TOTAL volume)) * Sum(nettprice), category, YearMonth))

[#4]

Hi Marcus,

thank you but there is no information regarding weighted average.

Best,

Krzysztof

[#5]

Hi Sunny,

thank you. I tried this. Using TOTAL returns me the same number for each month.

But I know that if I have e.g. 40 records in each category in structure:

     YearMonth, Category, Volume, NetPrice

I should have receive different weighted average for each sum:

     YearMonth      Category          weighted avg

     2016-06          Category1          199

     2016-07          Category1          195

     2016-06          Category2          205

     2016-07          Category2          201

Best,

Krzysztof

[sunny_talwar]

Hi Krzysztof -

I am not sure how you are arriving at the numbers you have listed above. Would it be possible to share some raw data with the expected output?

Best,

Sunny

[#7]

Hi Sunny,

attached you will find dummy data. Thank you. As you can see weighted average is correct... but when it is sum up.

Best,
Krzysztof

[sunny_talwar]

I guess I am still having trouble understanding what your desired goal is?

Is this the correct desired goal you are looking to achieve? This seems to be repeating the weighted average number for each month for a particular category.

I think your sample is good, I just need to know what the expected output needs to be in order to provide any help from here.

Best,

Sunny

[sunny_talwar]

Is this correct?

What exactly do you want to see differently here?

[#10]

No, this is incorrect. Look, weighted average should be counted: price1*sales1+price2*sales2... / Total sum(sales) for category and month, thus for category1 and 2016-06 should be: 200*195+230*340+245*150 / 195+340+150 = 224,74.

So, standard average: 225,00; weighted average: 224,74.