How to stop calculating average of averages

[mbd]

Hi, 

Hi,

I'm working on averaging data. I'm averaging inventory data over a time period. The time period and the items are selected in Slicers.

Here's the equation I'm using 

AvgInv = Calculate(average(vQuery1[InvQty]), allselected(vQuery1))

The equation works great when I select only one item to average.

But when I select more than one item the equation does not work. 

It is summing the average of each item and then averaging that for the average of both.

When I select more than one item I want the sum of the average for each item not the average of the averages. 

Here's some sample data from the chart. The first three columns are an export from the chart for Item1, the next three are an export for Item2, and the last three are an export when  both items 1 and 2 are selected. So my average should be 5128 (5057+71), not 2564 as shown in the last column.

Date	Item1InvQty	AvgInv		Date	Item2InvQty	AvgInv		Date	Item1&2InvQty	AvgInv
8/24/2009	5510.44	5057.33		8/24/2009	42.45	71.13		8/24/2009	5552.89	2564.23
8/31/2009	4116.87	5057.33		8/31/2009	29.74	71.13		8/31/2009	4146.61	2564.23
9/7/2009	5779.86	5057.33		9/7/2009	21.75	71.13		9/7/2009	5801.61	2564.23
9/14/2009	5207.16	5057.33		9/14/2009	19.87	71.13		9/14/2009	5227.03	2564.23
9/21/2009	6110.30	5057.33		9/21/2009	13.55	71.13		9/21/2009	6123.85	2564.23
9/28/2009	5129.99	5057.33		9/28/2009	74.96	71.13		9/28/2009	5204.95	2564.23
10/5/2009	4400.31	5057.33		10/5/2009	69.88	71.13		10/5/2009	4470.19	2564.23
10/12/2009	4588.06	5057.33		10/12/2009	56.96	71.13		10/12/2009	4645.02	2564.23
10/19/2009	4936.42	5057.33		10/19/2009	53.33	71.13		10/19/2009	4989.75	2564.23
10/26/2009	5183.44	5057.33		10/26/2009	44.04	71.13		10/26/2009	5227.48	2564.23
11/2/2009	5085.86	5057.33		11/2/2009	83.24	71.13		11/2/2009	5169.10	2564.23
11/9/2009	5180.09	5057.33		11/9/2009	172.66	71.13		11/9/2009	5352.75	2564.23
11/16/2009	4585.29	5057.33		11/16/2009	171.12	71.13		11/16/2009	4756.41	2564.23
11/23/2009	5227.72	5057.33		11/23/2009	160.9	71.13		11/23/2009	5388.62	2564.23
11/30/2009	5143.79	5057.33		11/30/2009	150.6	71.13		11/30/2009	5294.39	2564.23
12/7/2009	5636.72	5057.33		12/7/2009	148.41	71.13		12/7/2009	5785.13	2564.23
12/14/2009	4826.77	5057.33		12/14/2009	165.83	71.13		12/14/2009	4992.60	2564.23
12/21/2009	4428.52	5057.33		12/21/2009	159.23	71.13		12/21/2009	4587.75	2564.23
12/28/2009	4011.84	5057.33		12/28/2009	150.3	71.13		12/28/2009	4162.14	2564.23
1/4/2010	4066.18	5057.33		1/4/2010	138.9	71.13		1/4/2010	4205.08	2564.23
1/11/2010	4403.92	5057.33		1/11/2010	139.55	71.13		1/11/2010	4543.47	2564.23
1/18/2010	3470.68	5057.33		1/18/2010	112.56	71.13		1/18/2010	3583.24	2564.23
1/25/2010	5107.09	5057.33		1/25/2010	109.14	71.13		1/25/2010	5216.23	2564.23
2/1/2010	5279.14	5057.33		2/1/2010	93.69	71.13		2/1/2010	5372.83	2564.23
2/8/2010	4566.76	5057.33		2/8/2010	86.94	71.13		2/8/2010	4653.70	2564.23
2/15/2010	3416.20	5057.33		2/15/2010	83.96	71.13		2/15/2010	3500.16	2564.23
2/22/2010	4686.53	5057.33		2/22/2010	75.4	71.13		2/22/2010	4761.93	2564.23
3/1/2010	5520.13	5057.33		3/1/2010	73.22	71.13		3/1/2010	5593.35	2564.23
3/8/2010	4988.31	5057.33		3/8/2010	66.69	71.13		3/8/2010	5055.00	2564.23
3/15/2010	5300.54	5057.33		3/15/2010	63.2	71.13		3/15/2010	5363.74	2564.23
3/22/2010	4707.98	5057.33		3/22/2010	100.87	71.13		3/22/2010	4808.85	2564.23
3/29/2010	5275.72	5057.33		3/29/2010	97.03	71.13		3/29/2010	5372.75	2564.23
4/5/2010	4380.07	5057.33		4/5/2010	83.6	71.13		4/5/2010	4463.67	2564.23
4/12/2010	4545.17	5057.33		4/12/2010	71.11	71.13		4/12/2010	4616.28	2564.23
4/19/2010	5161.18	5057.33		4/19/2010	80.11	71.13		4/19/2010	5241.29	2564.23
4/26/2010	5015.45	5057.33		4/26/2010	70.75	71.13		4/26/2010	5086.20	2564.23
5/3/2010	5134.16	5057.33		5/3/2010	59.28	71.13		5/3/2010	5193.44	2564.23
5/10/2010	4934.22	5057.33		5/10/2010	17.41	71.13		5/10/2010	4951.63	2564.23
5/17/2010	4959.90	5057.33		5/17/2010	17.62	71.13		5/17/2010	4977.52	2564.23
5/24/2010	5058.76	5057.33		5/24/2010	10.15	71.13		5/24/2010	5068.91	2564.23
5/31/2010	4433.95	5057.33		5/31/2010	7.03	71.13		5/31/2010	4440.98	2564.23
6/7/2010	4990.50	5057.33		6/7/2010	6.38	71.13		6/7/2010	4996.88	2564.23
6/14/2010	4714.10	5057.33		6/14/2010	69.01	71.13		6/14/2010	4783.11	2564.23
6/21/2010	5054.95	5057.33		6/21/2010	61.4	71.13		6/21/2010	5116.35	2564.23
6/28/2010	5443.07	5057.33		6/28/2010	43.61	71.13		6/28/2010	5486.68	2564.23
7/5/2010	5292.94	5057.33		7/5/2010	36.86	71.13		7/5/2010	5329.80	2564.23
7/12/2010	5744.98	5057.33		7/12/2010	34.18	71.13		7/12/2010	5779.16	2564.23
7/19/2010	5584.79	5057.33		7/19/2010	26.27	71.13		7/19/2010	5611.06	2564.23
7/26/2010	6221.12	5057.33		7/26/2010	20.02	71.13		7/26/2010	6241.14	2564.23
8/2/2010	5288.46	5057.33		8/2/2010	14.28	71.13		8/2/2010	5302.74	2564.23
8/9/2010	6577.39	5057.33		8/9/2010	7.6	71.13		8/9/2010	6584.99	2564.23
8/16/2010	6828.84	5057.33		8/16/2010	2.82	71.13		8/16/2010	6831.66	2564.23
8/23/2010	6795.96	5057.33		8/23/2010	0.43	71.13		8/23/2010	6796.39	2564.23

[Eric_Zhang · Accepted solution]

@mbd

Can you post some raw data instead of the data in chart? Roughly you could try

AvgInv =
CALCULATE ( AVERAGE ( vQuery1[InvQty] ), ALLSELECTED ( vQuery1 ) )
    * DISTINCTCOUNT ( vQuery1[Item] )

[mbd]

Thanks Eric, the formula you provided solved my problem. 

Is there a way I can show the data label for just the average inventory I've calculated and not all the other data in the graph?