I have a pivot table that I want to calculate a weighted avg subtotal for. Can someone please help?

[Anonymous]

I need to calculate a weighted subtotal for the Total column.

Calculation for Weight:

sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name> TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)

Percent:

sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name> TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk])

Total:

Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name> TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk])))

I would like to be able to drop the Weight and Percent columns and just use the Total Column.  Market 1 total should be $873. Market 2 should be $861, or the sum of the percent column.  I know I'm close....

Market	County	TotalMM	Bnchmrk	Weight	Percent	Total
1	Market Total	2239		100.00%		$5,235
1	A	276	$846	12.33%	$104	$846
1	B	590	$881	26.35%	$232	$881
1	C	58	$891	2.59%	$23	$891
1	D	392	$843	17.51%	$148	$843
1	E	392	$891	17.51%	$156	$891
1	F	531	$883	23.72%	$209	$883
2	Market Total	2229		100.00%		$2,574
2	G	454	$860	20.37%	$175	$860
2	H	1116	$874	50.07%	$437	$874
2	I	659	$840	29.56%	$248	$840

[vishsaggi]

Can you share a sample to look into? Or may be try like:

= Sum(Aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name> TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL<Market> TotalMM)*([Bnchmrk]), Market))

Can you also show what should be your expected output.

[sunny_talwar]

May be this

Avg(Aggr(

Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name>TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk]))),

Market))

[Anonymous]

Thanks Vishwarath,  I tried this formula, but it gave me all $0 for the total Column.  Here is what it should look like.

Market	County	TotalMM	Bnchmrk	Weight	Percent	Total
1	Market Total	2239		100.00%		$873
1	A	276	$846	12.33%	$104	$846
1	B	590	$881	26.35%	$232	$881
1	C	58	$891	2.59%	$23	$891
1	D	392	$843	17.51%	$148	$843
1	E	392	$891	17.51%	$156	$891
1	F	531	$883	23.72%	$209	$883
2	Market Total	2229		100.00%		$861
2	G	454	$860	20.37%	$175	$860
2	H	1116	$874	50.07%	$437	$874
2	I	659	$840	29.56%	$248	$840

[Anonymous]

Thanks Sunny.  this just gave me a total of the County Benchmarks.  They counties were null.

[sunny_talwar]

How about this

Avg(TOTAL <Market> Aggr(

Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name>TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk]))),

Market, County))

[vishsaggi]

Try this:

= Sum(TOTAL <Market, County_name> Aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name> TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL<Market> TotalMM)*([Bnchmrk]), Market))

Please also try Sunny's expression. If you still do not see what you expected, please do share your qvw for us to look into.

[Anonymous]

This is closer.  It populated all the counties, but it gave me all the same number.  $835.

[Anonymous]

I figured it out.  Needed to add County to the first part.  THANK YOU so much for your help!!!!!

Avg(TOTAL <Market,County_Name> Aggr(

Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name>TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk]))),

Market, County))

[sunny_talwar · Accepted solution]

Then in that case... try this as Total is redundant

Avg(Aggr(

Sum(Total<Market,County_Name>(aggr(sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market,County_Name>TotalMM)/sum({$<Cov_Mth_Date={'12/31/2017'}>}TOTAL <Market> TotalMM)*([Bnchmrk]),[Bnchmrk]))),

Market, County))