Forum Discussion
Average weekly sales
I have sales data in weekly buckets. I need to calculate two measures using the sales data. The first measure is to calculate each week's total sales and divide by the average weekly sales for that...
Ashish_Mathur, I don't have a good way to share a link, so I've pasted the data below. Let me know if this doesn't work.
Measure 1 = E2/AVERAGEIFS($E:$E,$D:$D,D2)
Measure 2 = AVERAGEIFS($F:$F,$C:$C,C2)
| Product | Date | fiscal week num | year | Sales | Measure 1 | Measure 2 |
| A | Saturday, January 3, 2015 | 1 | 2015 | 1014 | 0.6401 | 0.6464 |
| A | Saturday, January 10, 2015 | 2 | 2015 | 1137 | 0.7178 | 0.7294 |
| A | Saturday, January 17, 2015 | 3 | 2015 | 2096 | 1.3231 | 1.3342 |
| A | Saturday, January 24, 2015 | 4 | 2015 | 1338 | 0.8446 | 0.8524 |
| A | Saturday, January 31, 2015 | 5 | 2015 | 1844 | 1.1641 | 1.1742 |
| A | Saturday, February 7, 2015 | 6 | 2015 | 2459 | 1.5523 | 1.5673 |
| A | Saturday, February 14, 2015 | 7 | 2015 | 1379 | 0.8705 | 0.8771 |
| A | Saturday, February 21, 2015 | 8 | 2015 | 1137 | 0.7178 | 0.7262 |
| A | Saturday, February 28, 2015 | 9 | 2015 | 2498 | 1.5769 | 1.5871 |
| A | Saturday, March 7, 2015 | 10 | 2015 | 1919 | 1.2114 | 1.2253 |
| A | Saturday, March 14, 2015 | 11 | 2015 | 2212 | 1.3964 | 1.4077 |
| A | Saturday, March 21, 2015 | 12 | 2015 | 900 | 0.5681 | 0.5748 |
| A | Saturday, March 28, 2015 | 13 | 2015 | 1794 | 1.1325 | 1.1434 |
| A | Saturday, April 4, 2015 | 14 | 2015 | 1453 | 0.9172 | 0.9282 |
| A | Saturday, April 11, 2015 | 15 | 2015 | 1232 | 0.7777 | 0.7885 |
| A | Saturday, April 18, 2015 | 16 | 2015 | 2223 | 1.4033 | 1.4119 |
| A | Saturday, April 25, 2015 | 17 | 2015 | 1492 | 0.9419 | 0.9510 |
| A | Saturday, May 2, 2015 | 18 | 2015 | 2023 | 1.2771 | 1.2835 |
| A | Saturday, May 9, 2015 | 19 | 2015 | 1408 | 0.8888 | 0.8966 |
| A | Saturday, May 16, 2015 | 20 | 2015 | 2065 | 1.3036 | 1.3095 |
| A | Saturday, May 23, 2015 | 21 | 2015 | 889 | 0.5612 | 0.5698 |
| A | Saturday, May 30, 2015 | 22 | 2015 | 1569 | 0.9905 | 1.0002 |
| A | Saturday, June 6, 2015 | 23 | 2015 | 2285 | 1.4424 | 1.4522 |
| A | Saturday, June 13, 2015 | 24 | 2015 | 1268 | 0.8004 | 0.8081 |
| A | Saturday, June 20, 2015 | 25 | 2015 | 735 | 0.4640 | 0.4712 |
| A | Saturday, June 27, 2015 | 26 | 2015 | 2314 | 1.4608 | 1.4720 |
| A | Saturday, July 4, 2015 | 27 | 2015 | 564 | 0.3560 | 0.3637 |
| A | Saturday, July 11, 2015 | 28 | 2015 | 2436 | 1.5378 | 1.5480 |
| A | Saturday, July 18, 2015 | 29 | 2015 | 1794 | 1.1325 | 1.1442 |
| A | Saturday, July 25, 2015 | 30 | 2015 | 1491 | 0.9412 | 0.9496 |
| A | Saturday, August 1, 2015 | 31 | 2015 | 1111 | 0.7013 | 0.7085 |
| A | Saturday, August 8, 2015 | 32 | 2015 | 805 | 0.5082 | 0.5145 |
| A | Saturday, August 15, 2015 | 33 | 2015 | 1637 | 1.0334 | 1.0433 |
| A | Saturday, August 22, 2015 | 34 | 2015 | 530 | 0.3346 | 0.3445 |
| A | Saturday, August 29, 2015 | 35 | 2015 | 2292 | 1.4469 | 1.4554 |
| A | Saturday, September 5, 2015 | 36 | 2015 | 2310 | 1.4582 | 1.4627 |
| A | Saturday, September 12, 2015 | 37 | 2015 | 2064 | 1.3029 | 1.3081 |
| A | Saturday, September 19, 2015 | 38 | 2015 | 1351 | 0.8528 | 0.8575 |
| A | Saturday, September 26, 2015 | 39 | 2015 | 914 | 0.5770 | 0.5840 |
| A | Saturday, October 3, 2015 | 40 | 2015 | 2308 | 1.4570 | 1.4646 |
| A | Saturday, October 10, 2015 | 41 | 2015 | 1995 | 1.2594 | 1.2651 |
| A | Saturday, October 17, 2015 | 42 | 2015 | 2339 | 1.4765 | 1.4845 |
| A | Saturday, October 24, 2015 | 43 | 2015 | 1550 | 0.9785 | 0.9878 |
| A | Saturday, October 31, 2015 | 44 | 2015 | 958 | 0.6048 | 0.6099 |
| A | Saturday, November 7, 2015 | 45 | 2015 | 1689 | 1.0662 | 1.0716 |
| A | Saturday, November 14, 2015 | 46 | 2015 | 1569 | 0.9905 | 0.9973 |
| A | Saturday, November 21, 2015 | 47 | 2015 | 1332 | 0.8408 | 0.8490 |
| A | Saturday, November 28, 2015 | 48 | 2015 | 703 | 0.4438 | 0.4474 |
| A | Saturday, December 5, 2015 | 49 | 2015 | 850 | 0.5366 | 0.5394 |
| A | Saturday, December 12, 2015 | 50 | 2015 | 792 | 0.5000 | 0.5056 |
| A | Saturday, December 19, 2015 | 51 | 2015 | 2123 | 1.3402 | 1.3438 |
| A | Saturday, December 26, 2015 | 52 | 2015 | 2184 | 1.3787 | 1.3818 |
| A | Saturday, January 2, 2016 | 1 | 2016 | 1015 | 0.6430 | 0.6464 |
| A | Saturday, January 9, 2016 | 2 | 2016 | 1161 | 0.7355 | 0.7294 |
| A | Saturday, January 16, 2016 | 3 | 2016 | 2119 | 1.3424 | 1.3342 |
| A | Saturday, January 23, 2016 | 4 | 2016 | 1350 | 0.8552 | 0.8524 |
| A | Saturday, January 30, 2016 | 5 | 2016 | 1856 | 1.1758 | 1.1742 |
| A | Saturday, February 6, 2016 | 6 | 2016 | 2484 | 1.5736 | 1.5673 |
| A | Saturday, February 13, 2016 | 7 | 2016 | 1386 | 0.8780 | 0.8771 |
| A | Saturday, February 20, 2016 | 8 | 2016 | 1141 | 0.7228 | 0.7262 |
| A | Saturday, February 27, 2016 | 9 | 2016 | 2517 | 1.5945 | 1.5871 |
| A | Saturday, March 5, 2016 | 10 | 2016 | 1943 | 1.2309 | 1.2253 |
| A | Saturday, March 12, 2016 | 11 | 2016 | 2229 | 1.4121 | 1.4077 |
| A | Saturday, March 19, 2016 | 12 | 2016 | 906 | 0.5740 | 0.5748 |
| A | Saturday, March 26, 2016 | 13 | 2016 | 1806 | 1.1441 | 1.1434 |
| A | Saturday, April 2, 2016 | 14 | 2016 | 1478 | 0.9363 | 0.9282 |
| A | Saturday, April 9, 2016 | 15 | 2016 | 1253 | 0.7938 | 0.7885 |
| A | Saturday, April 16, 2016 | 16 | 2016 | 2230 | 1.4127 | 1.4119 |
| A | Saturday, April 23, 2016 | 17 | 2016 | 1511 | 0.9572 | 0.9510 |
| A | Saturday, April 30, 2016 | 18 | 2016 | 2026 | 1.2835 | 1.2835 |
| A | Saturday, May 7, 2016 | 19 | 2016 | 1420 | 0.8996 | 0.8966 |
| A | Saturday, May 14, 2016 | 20 | 2016 | 2067 | 1.3094 | 1.3095 |
| A | Saturday, May 21, 2016 | 21 | 2016 | 902 | 0.5714 | 0.5698 |
| A | Saturday, May 28, 2016 | 22 | 2016 | 1593 | 1.0092 | 1.0002 |
| A | Saturday, June 4, 2016 | 23 | 2016 | 2289 | 1.4501 | 1.4522 |
| A | Saturday, June 11, 2016 | 24 | 2016 | 1282 | 0.8121 | 0.8081 |
| A | Saturday, June 18, 2016 | 25 | 2016 | 756 | 0.4789 | 0.4712 |
| A | Saturday, June 25, 2016 | 26 | 2016 | 2320 | 1.4697 | 1.4720 |
| A | Saturday, July 2, 2016 | 27 | 2016 | 576 | 0.3649 | 0.3637 |
| A | Saturday, July 9, 2016 | 28 | 2016 | 2455 | 1.5552 | 1.5480 |
| A | Saturday, July 16, 2016 | 29 | 2016 | 1811 | 1.1473 | 1.1442 |
| A | Saturday, July 23, 2016 | 30 | 2016 | 1498 | 0.9490 | 0.9496 |
| A | Saturday, July 30, 2016 | 31 | 2016 | 1132 | 0.7171 | 0.7085 |
| A | Saturday, August 6, 2016 | 32 | 2016 | 824 | 0.5220 | 0.5145 |
| A | Saturday, August 13, 2016 | 33 | 2016 | 1659 | 1.0510 | 1.0433 |
| A | Saturday, August 20, 2016 | 34 | 2016 | 555 | 0.3516 | 0.3445 |
| A | Saturday, August 27, 2016 | 35 | 2016 | 2311 | 1.4640 | 1.4554 |
| A | Saturday, September 3, 2016 | 36 | 2016 | 2316 | 1.4672 | 1.4627 |
| A | Saturday, September 10, 2016 | 37 | 2016 | 2073 | 1.3132 | 1.3081 |
| A | Saturday, September 17, 2016 | 38 | 2016 | 1361 | 0.8622 | 0.8575 |
| A | Saturday, September 24, 2016 | 39 | 2016 | 933 | 0.5911 | 0.5840 |
| A | Saturday, October 1, 2016 | 40 | 2016 | 2324 | 1.4723 | 1.4646 |
| A | Saturday, October 8, 2016 | 41 | 2016 | 2006 | 1.2708 | 1.2651 |
| A | Saturday, October 15, 2016 | 42 | 2016 | 2356 | 1.4925 | 1.4845 |
| A | Saturday, October 22, 2016 | 43 | 2016 | 1574 | 0.9971 | 0.9878 |
| A | Saturday, October 29, 2016 | 44 | 2016 | 971 | 0.6151 | 0.6099 |
| A | Saturday, November 5, 2016 | 45 | 2016 | 1700 | 1.0770 | 1.0716 |
| A | Saturday, November 12, 2016 | 46 | 2016 | 1585 | 1.0041 | 0.9973 |
| A | Saturday, November 19, 2016 | 47 | 2016 | 1353 | 0.8571 | 0.8490 |
| A | Saturday, November 26, 2016 | 48 | 2016 | 712 | 0.4511 | 0.4474 |
| A | Saturday, December 3, 2016 | 49 | 2016 | 856 | 0.5423 | 0.5394 |
| A | Saturday, December 10, 2016 | 50 | 2016 | 807 | 0.5112 | 0.5056 |
| A | Saturday, December 17, 2016 | 51 | 2016 | 2127 | 1.3475 | 1.3438 |
| A | Saturday, December 24, 2016 | 52 | 2016 | 2186 | 1.3848 | 1.3818 |
| A | Saturday, December 31, 2016 | 53 | 2016 | 561 | 0.3554 | 0.3554 |
| A | Saturday, January 7, 2017 | 1 | 2017 | 1033 | 0.6561 | 0.6464 |
| A | Saturday, January 14, 2017 | 2 | 2017 | 1157 | 0.7349 | 0.7294 |
| A | Saturday, January 21, 2017 | 3 | 2017 | 2105 | 1.3370 | 1.3342 |
| A | Saturday, January 28, 2017 | 4 | 2017 | 1350 | 0.8575 | 0.8524 |
| A | Saturday, February 4, 2017 | 5 | 2017 | 1862 | 1.1827 | 1.1742 |
| A | Saturday, February 11, 2017 | 6 | 2017 | 2481 | 1.5759 | 1.5673 |
| A | Saturday, February 18, 2017 | 7 | 2017 | 1390 | 0.8829 | 0.8771 |
| A | Saturday, February 25, 2017 | 8 | 2017 | 1162 | 0.7381 | 0.7262 |
| A | Saturday, March 4, 2017 | 9 | 2017 | 2503 | 1.5898 | 1.5871 |
| A | Saturday, March 11, 2017 | 10 | 2017 | 1942 | 1.2335 | 1.2253 |
| A | Saturday, March 18, 2017 | 11 | 2017 | 2227 | 1.4145 | 1.4077 |
| A | Saturday, March 25, 2017 | 12 | 2017 | 917 | 0.5825 | 0.5748 |
| A | Saturday, April 1, 2017 | 13 | 2017 | 1816 | 1.1535 | 1.1434 |
| A | Saturday, April 8, 2017 | 14 | 2017 | 1466 | 0.9312 | 0.9282 |
| A | Saturday, April 15, 2017 | 15 | 2017 | 1250 | 0.7940 | 0.7885 |
| A | Saturday, April 22, 2017 | 16 | 2017 | 2235 | 1.4196 | 1.4119 |
| A | Saturday, April 29, 2017 | 17 | 2017 | 1502 | 0.9540 | 0.9510 |
| A | Saturday, May 6, 2017 | 18 | 2017 | 2031 | 1.2900 | 1.2835 |
| A | Saturday, May 13, 2017 | 19 | 2017 | 1419 | 0.9013 | 0.8966 |
| A | Saturday, May 20, 2017 | 20 | 2017 | 2071 | 1.3154 | 1.3095 |
| A | Saturday, May 27, 2017 | 21 | 2017 | 908 | 0.5767 | 0.5698 |
| A | Saturday, June 3, 2017 | 22 | 2017 | 1576 | 1.0010 | 1.0002 |
| A | Saturday, June 10, 2017 | 23 | 2017 | 2305 | 1.4641 | 1.4522 |
| A | Saturday, June 17, 2017 | 24 | 2017 | 1278 | 0.8117 | 0.8081 |
| A | Saturday, June 24, 2017 | 25 | 2017 | 741 | 0.4707 | 0.4712 |
| A | Saturday, July 1, 2017 | 26 | 2017 | 2339 | 1.4857 | 1.4720 |
| A | Saturday, July 8, 2017 | 27 | 2017 | 583 | 0.3703 | 0.3637 |
| A | Saturday, July 15, 2017 | 28 | 2017 | 2442 | 1.5511 | 1.5480 |
| A | Saturday, July 22, 2017 | 29 | 2017 | 1815 | 1.1528 | 1.1442 |
| A | Saturday, July 29, 2017 | 30 | 2017 | 1509 | 0.9585 | 0.9496 |
| A | Saturday, August 5, 2017 | 31 | 2017 | 1113 | 0.7069 | 0.7085 |
| A | Saturday, August 12, 2017 | 32 | 2017 | 808 | 0.5132 | 0.5145 |
| A | Saturday, August 19, 2017 | 33 | 2017 | 1646 | 1.0455 | 1.0433 |
| A | Saturday, August 26, 2017 | 34 | 2017 | 547 | 0.3474 | 0.3445 |
Thank you Ashish_Mathur! While this works for the single product "A" that I've supplied in the sample data, I'm not able to replicate the same results once I apply this logic to my entire data set. I have thousands of products that are divided out into different product categories. When I attempt to add a second product "B", I get this error:
Data Column 'Date' in Table 'Data' contains a duplicate value '1/3/2015' and
this is not allowed for columns on the one side of a many-to-one
relationship or for columns that are used as the primary key of a table.Once everything is working properly, I ultimately will need to be able to use slicers to pick different individual products, or view total categories of products. I hope this helps.
Hi,
Share such a dataset and show me the expected result on that dataset.