Calculating the Average Weekly % Difference between Expected and Actual figures

[benwebber92]

I have the below table in Excel imported into Power BI as a query which shows the week number of a group of people's Predicted and Actual figures for a task. 

While I can easily create a matrix that shows the Total Predicted, Total Actual, Total Difference and % Difference for the figures for each week, I also need to be able to calculate the Average Weekly % Difference - in other words, the arithmetic mean all of the '% Difference' figures.

Does anybody know how this could be done in Power BI?

edit: to confirm, the second table in the post is actually a representation of the Matrix I've created in Power BI rather than a table created in Excel. 

Week	Person	Predicted	Actual
1	A	81	474
1	B	116	29
1	C	483	85
1	D	48	71
1		169	252
1	F	488	44
1	G	372	268
1	H	58	294
1	I	188	244
1	J	482	21
2	K	487	372
2	L	391	140
2	M	305	262
2	N	387	304
2	O	92	87
2	P	304	90
2	Q	10	330
2	R	130	103
3	S	342	406
3	T	494	307
3	U	168	110
3	V	209	386
3	W	236	424
3	X	473	365
4	Y	493	460
4	Z	301	366
4	AA	118	389
4	AB	251	474
4	AC	51	228
4	AD	356	68
4	AE	168	27
4	AF	205	251

Week	Predicted	Actual	Difference	% Difference
1	2485	1782	-703	-28.3%
2	2106	1688	-418	-19.8%
3	1922	1998	76	4.0%
4	1943	2263	320	16.5%
Grand Total	8456	7731	-725	-8.6%

Average weekly % Diff

-6.9%

[ausrine]

Wouldn't it be enough to create a measure with AVERAGE(%Diff)?

[benwebber92]

Sorry to confirm, the second table in the post is actually a representation of the Matrix I've created in Power BI rather than a table created in Excel.

Difference and % Difference are both measures that I created in Power BI.

[v-qiuyu-msft · Accepted solution]

Hi @benwebber92,

You can create a measure like below. For details, you can see the attached Average weekly % Diff.pbix file.

Average weekly % Diff =
AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table1',
            'Table1'[Week],
            "Predicted", SUM ( Table1[Predicted] ),
            "Actual", SUM ( Table1[Actual] ),
            "Diff", SUM ( Table1[Actual] ) - SUM ( Table1[Predicted] )
        ),
        "%Diff", [Diff] / [Predicted]
    ),
    [%Diff]
)

Best Regards,
QiuyunYu