topic Average of Calculation in DAX Commands and Tips

Average of Calculation

sgv2000 — Wed, 19 Aug 2020 13:55:33 GMT

I have what I think is a difficult request. We need to average calculations. Normally I would sum the base numbers the divide them out to get the total for any given period. In this case, that does not actually work. I need to sum the values to a given month, calculation a number then average that number over a year and display the result back on every month in that year so that it makes a straight line for the year then shifts for the next year.

Here is some data:

fiscalyear	snapshotdatekey	Group	A	B
2019	20180924	P1	$ 278,848.04	$4,488,189.24
2019	20181029	P1	$ 314,841.33	$6,477,121.20
2019	20181126	P1	$ 309,689.75	$6,716,298.99
2019	20181231	P1	$ 411,515.40	$8,887,870.01
2019	20190128	P1	$ 457,607.73	$7,708,110.00
2019	20190225	P1	$ 469,853.25	$6,529,844.70
2019	20190325	P1	$ 483,040.16	$7,316,726.42
2019	20190429	P1	$ 532,521.67	$8,871,548.88
2019	20190527	P1	$ 488,893.50	$8,709,589.21
2019	20190624	P1	$ 370,846.40	$9,130,070.90
2019	20190729	P1	$ 316,385.76	$8,639,870.17
2019	20190826	P1	$ 277,852.89	$6,775,670.11
2020	20190930	P1	$ 206,350.87	$7,407,926.56
2020	20191028	P1	$ 168,217.30	$6,296,450.38
2020	20191125	P1	$ 128,043.85	$4,695,644.48
2020	20191230	P1	$ 137,252.77	$6,533,805.23
2020	20200127	P1	$ 141,225.38	$5,181,490.87
2020	20200224	P1	$ 115,903.77	$3,595,357.29
2020	20200330	P1	$ 162,153.56	$4,935,011.97
2020	20200427	P1	$ 163,697.04	$4,113,838.07
2020	20200525	P1	$ 146,603.43	$2,695,020.73
2020	20200629	P1	$ 148,181.73	$4,015,751.93
2020	20200727	P1	$ 109,965.43	$3,313,957.44
2020	20200803	P1	$ 102,650.46	$2,901,466.65
2019	20180924	P2	$ 1,833,892.58	$24,782,303.00
2019	20181029	P2	$ 1,308,628.59	$24,446,044.97
2019	20181126	P2	$ 614,329.80	$16,365,929.97
2019	20181231	P2	$ 787,822.44	$17,958,706.00
2019	20190128	P2	$ 905,548.97	$12,994,856.05
2019	20190225	P2	$ 281,210.11	$3,378,642.30
2019	20190325	P2	$ 339,094.48	$5,724,809.81
2019	20190429	P2	$ 335,269.92	$3,476,134.18
2019	20190527	P2	$ 674,804.58	$4,794,038.25
2019	20190624	P2	$ 1,905,069.16	$8,024,551.28
2019	20190729	P2	$ 1,705,701.75	$12,783,285.09
2019	20190826	P2	$ 2,257,869.43	$13,595,708.62
2020	20190930	P2	$ 1,768,804.75	$21,888,151.78
2020	20191028	P2	$ 1,505,156.89	$20,065,604.62
2020	20191125	P2	$ 1,142,695.30	$21,747,013.37
2020	20191230	P2	$ 1,016,565.63	$16,328,356.58
2020	20200127	P2	$ 1,475,249.14	$19,385,685.16
2020	20200224	P2	$ 3,230,211.51	$21,305,733.15
2020	20200330	P2	$ 3,240,043.33	$27,947,612.44
2020	20200427	P2	$ 1,116,678.26	$14,564,844.67
2020	20200525	P2	$ 1,863,047.40	$12,153,308.15
2020	20200629	P2	$ 1,584,257.23	$15,218,155.79
2020	20200727	P2	$ 2,106,581.48	$24,287,245.79
2020	20200803	P2	$ 2,860,393.34	$23,419,627.99

Re: Average of Calculation

sgv2000 — Wed, 19 Aug 2020 13:58:25 GMT

Here is the result I am looking for:

Calculation for DOS = B/A

fiscalyear	snapshotdatekey	A	B	DOS	Average DOS	Yearly Average
2019	20180924	$ 2,112,740.62	$29,270,492.24	13.85	13.85	14.71
2019	20181029	$ 1,623,469.92	$30,923,166.16	19.05	19.05	14.71
2019	20181126	$ 924,019.55	$23,082,228.95	24.98	24.98	14.71
2019	20181231	$ 1,199,337.83	$26,846,576.01	22.38	22.38	14.71
2019	20190128	$ 1,363,156.69	$20,702,966.05	15.19	15.19	14.71
2019	20190225	$ 751,063.36	$9,908,487.00	13.19	13.19	14.71
2019	20190325	$ 822,134.63	$13,041,536.23	15.86	15.86	14.71
2019	20190429	$ 867,791.59	$12,347,683.07	14.23	14.23	14.71
2019	20190527	$ 1,163,698.08	$13,503,627.47	11.60	11.60	14.71
2019	20190624	$ 2,275,915.56	$17,154,622.18	7.54	7.54	14.71
2019	20190729	$ 2,022,087.51	$21,423,155.26	10.59	10.59	14.71
2019	20190826	$ 2,535,722.32	$20,371,378.73	8.03	8.03	14.71
2020	20190930	$ 1,975,155.62	$29,296,078.34	14.83	14.83	13.16
2020	20191028	$ 1,673,374.19	$26,362,054.99	15.75	15.75	13.16
2020	20191125	$ 1,270,739.15	$26,442,657.85	20.81	20.81	13.16
2020	20191230	$ 1,153,818.40	$22,862,161.81	19.81	19.81	13.16
2020	20200127	$ 1,616,474.53	$24,567,176.03	15.20	15.20	13.16
2020	20200224	$ 3,346,115.28	$24,901,090.43	7.44	7.44	13.16
2020	20200330	$ 3,402,196.89	$32,882,624.41	9.67	9.67	13.16
2020	20200427	$ 1,280,375.31	$18,678,682.74	14.59	14.59	13.16
2020	20200525	$ 2,009,650.83	$14,848,328.88	7.39	7.39	13.16
2020	20200629	$ 1,732,438.96	$19,233,907.72	11.10	11.10	13.16
2020	20200727	$ 2,216,546.90	$27,601,203.23	12.45	12.45	13.16
2020	20200803	$ 2,963,043.80	$26,321,094.63	8.88	8.88	13.16
					13.93

Re: Average of Calculation

Anonymous — Wed, 19 Aug 2020 15:44:11 GMT

Yeah... That's all very good and well but it would be even better if you could explain in plain English how calculations are performed. Your description above is not absolutely clear. On the other hand, figuring this out from your tables is not something anyone would have time to do. Please explain the calculations in the second table. Thanks.

Re: Average of Calculation

sgv2000 — Wed, 19 Aug 2020 16:46:10 GMT

Anonymous , Sure. If you pull the data from the original table into Power BI then put the values into a grid, doing nothing but letting it sum, you will get fiscalyear, snapshotdatekey, a and b. A and B are just sums. DOS is A/B, as explained. Average DOS is just that. It is the average of the DOS column. So on a given week it matches. The total for that column is the actual average of the DOS numbers. The last column is the most difficult. It is the average of the DOS values for a fiscslyear repeated on every row for that year. Hope that helps.

Re: Average of Calculation

amitchandak — Wed, 19 Aug 2020 17:36:47 GMT

sgv2000 , Try like

calculate(averageX(values(Table[snapshotdatekey]), divide(Table[B],Table[A])), all(Table))

Re: Average of Calculation

sgv2000 — Thu, 20 Aug 2020 14:23:09 GMT

amitchandak , this put me on the right path. I will mark this as the solution. It took a little playing with the filter condition to get it.

CALCULATE(AVERAGEX(VALUES(Table[snapshotdatekey]),divide(Table[B],Table[A]))),FILTER(ALLSELECTED(Table),Table[fiscalyear]=max(Table[fiscalyear])))