Calculating an Average on a Line Chart

[mbd]

Hi, 

I'm calculating an average on a line chart as follows:

AvgItemA = Calculate(average(vPlantItemWklyData[WklyItemA]), allselected(vPlantItemWklyData))

I've used this form on another average in another chart and it works great. 

I this line chart I'm getting an incorrect answer. PowerBI is returning an average of 0.95 and the answer should be 1.62.

What am I doing wrong?

Thanks

Here's my data

Date	Item	WklyItemA
9/14/2009 0:00	ItemA	1.945312
10/5/2009 0:00	ItemA	1.894224
11/2/2009 0:00	ItemA	1.762736
11/4/2009 0:00	ItemA	1.762736
11/30/2009 0:00	ItemA	1.779186
1/7/2010 0:00	ItemA	1.801572
1/8/2010 0:00	ItemA	0.900786
2/1/2010 0:00	ItemA	1.998774
3/1/2010 0:00	ItemA	1.95201
3/29/2010 0:00	ItemA	1.986208
4/5/2010 0:00	ItemA	0.976768
4/26/2010 0:00	ItemA	1.998508
5/10/2010 0:00	ItemA	0.995925
5/25/2010 0:00	ItemA	0.994673
5/26/2010 0:00	ItemA	0.994673
6/7/2010 0:00	ItemA	1.9206
6/8/2010 0:00	ItemA	0.9603
6/21/2010 0:00	ItemA	1.908812
7/6/2010 0:00	ItemA	1.947682
7/19/2010 0:00	ItemA	0.993915
8/2/2010 0:00	ItemA	1.945164
8/16/2010 0:00	ItemA	1.861134
8/30/2010 0:00	ItemA	1.932866

[mbd · Accepted solution]

Your questions led me to my solution. 

I was summing data points for the week that should not have been summed. Then when I calculated the average it averaged all the data points. Once I correct this I got the correct average.

Thanks!

[v-ljerr-msft]

Hi @mbd,

Based on my test, the result of the measure is 1.62. 

What column are you showing as Axis on the Line Chart? Do you have any Slicer applied on your report? Could you share a sample pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[mbd · Accepted solution]

Your questions led me to my solution. 

I was summing data points for the week that should not have been summed. Then when I calculated the average it averaged all the data points. Once I correct this I got the correct average.

Thanks!

[v-ljerr-msft · Accepted solution]

Hi @mbd,

Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread? 

Regards

[mbd]

Hi, 

I'm still 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. 

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

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