Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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!
Hi @mbd,
Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread?
Regards
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
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!
Hi @mbd,
Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread?
Regards
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 |
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |