Correctly calculate matrix subtotal as average of percent changes of ratios

[user01]

I have a table called 'sampdata'. I want to display a matrix of Values, Adjusted Values, and Percent Change by Item by FiscalYear. I want the subtotal to be the the average. It seems to work for all the Items except ExpenseRatio. How can I calculate the average for the ratio correctly?

I have two what-if parameters: Factor and Adj.

Factor = GENERATESERIES(0, 0.505, 0.005)

Adj = GENERATESERIES(0, 50, 1)

Factor = 10%. Adj = 10.

I have two filters from sampdata: Company and Item.

Company Filter = X. Item Filter excludes Revenue.

Measures: (Minor note: My actual data has more Items than the dummy data provided below.)

ValueM = IF(ISINSCOPE(sampdata[FiscalYear]),SUM(sampdata[VALUE]),AVERAGE(sampdata[VALUE]))

ValueM_Adj = 
 var _item = SELECTEDVALUE(sampdata[ITEM])
 return
    IF( _item ="NetIncome"
            , [ValueM]-(1-[Factor Value])*[Adj Value]
        , IF( _item ="Expenses"
            , [ValueM]+[Adj Value]
        , IF( _item = "Revenue"
            , [ValueM]
        , IF( _item ="ExpenseRatio"
            , IF(ISINSCOPE(sampdata[FiscalYear])
                , [ValueM]+[Adj Value]/CALCULATE([ValueM],REMOVEFILTERS(sampdata[ITEM]),sampdata[ITEM]="Revenue")
                , [ValueM]+[Adj Value]*AVERAGEX(CALCULATETABLE(sampdata, REMOVEFILTERS(sampdata[ITEM]),sampdata[ITEM]="Revenue"),1/[VALUE])
            )
        , [ValueM]-[Adj Value]
    ))))

%Change = AVERAGEX(sampdata, [ValueM_Adj]/[ValueM]-1)

The data set is below. I will post the desired results in a follow-up response.

Data set

ID	Company	FiscalYear	ITEM	VALUE
1	A	2019	Revenue	980.815
1	A	2019	NetIncome	886.813
1	A	2019	Expenses	94.002
1	A	2019	ExpenseRatio	0.096
2	B	2019	Revenue	801.511
2	B	2019	NetIncome	727.985
2	B	2019	Expenses	73.526
2	B	2019	ExpenseRatio	0.092
3	C	2019	Revenue	1,133.077
3	C	2019	NetIncome	1,024.482
3	C	2019	Expenses	108.595
3	C	2019	ExpenseRatio	0.096
4	D	2019	Revenue	533.449
4	D	2019	NetIncome	481.018
4	D	2019	Expenses	52.431
4	D	2019	ExpenseRatio	0.098
6	F	2019	Revenue	1,631.417
6	F	2019	NetIncome	1,475.061
6	F	2019	Expenses	156.356
6	F	2019	ExpenseRatio	0.096
7	G	2019	Revenue	1,006.241
7	G	2019	NetIncome	908.160
7	G	2019	Expenses	98.081
7	G	2019	ExpenseRatio	0.097
8	H	2019	Revenue	1,307.047
8	H	2019	NetIncome	1,179.645
8	H	2019	Expenses	127.402
8	H	2019	ExpenseRatio	0.097
9	I	2019	Revenue	464.876
9	I	2019	NetIncome	419.563
9	I	2019	Expenses	45.313
9	I	2019	ExpenseRatio	0.097
10	J	2019	Revenue	513.205
10	J	2019	NetIncome
10	J	2019	Expenses	513.205
10	J	2019	ExpenseRatio	1.000
11	K	2019	Revenue	920.113
11	K	2019	NetIncome	831.177
11	K	2019	Expenses	88.936
11	K	2019	ExpenseRatio	0.097
12	L	2019	Revenue	1,460.301
12	L	2019	NetIncome	1,322.736
12	L	2019	Expenses	137.565
12	L	2019	ExpenseRatio	0.094
13	M	2019	Revenue	410.461
13	M	2019	NetIncome	370.118
13	M	2019	Expenses	40.343
13	M	2019	ExpenseRatio	0.098
14	N	2019	Revenue	960.058
14	N	2019	NetIncome	865.697
14	N	2019	Expenses	94.361
14	N	2019	ExpenseRatio	0.098
15	O	2019	Revenue	1,752.153
15	O	2019	NetIncome	1,588.534
15	O	2019	Expenses	163.619
15	O	2019	ExpenseRatio	0.093
88	X	2019	Revenue	415.256
88	X	2019	NetIncome	375.118
88	X	2019	Expenses	40.138
88	X	2019	ExpenseRatio	0.097
1	A	2020	Revenue	1,005.182
1	A	2020	NetIncome	909.667
1	A	2020	Expenses	95.515
1	A	2020	ExpenseRatio	0.095
2	B	2020	Revenue	111.589
2	B	2020	NetIncome	100.531
2	B	2020	Expenses	11.058
2	B	2020	ExpenseRatio	0.099
3	C	2020	Revenue	9.866
3	C	2020	NetIncome	8.904
3	C	2020	Expenses	0.962
3	C	2020	ExpenseRatio	0.098
4	D	2020	Revenue	824.595
4	D	2020	NetIncome	744.892
4	D	2020	Expenses	79.703
4	D	2020	ExpenseRatio	0.097
5	E	2020	Revenue	1,168.972
5	E	2020	NetIncome	1,061.737
5	E	2020	Expenses	107.235
5	E	2020	ExpenseRatio	0.092
6	F	2020	Revenue	210.248
6	F	2020	NetIncome	190.961
6	F	2020	Expenses	19.287
6	F	2020	ExpenseRatio	0.092
7	G	2020	Revenue	928.062
7	G	2020	NetIncome	836.846
7	G	2020	Expenses	91.216
7	G	2020	ExpenseRatio	0.098
8	H	2020	Revenue	529.098
8	H	2020	NetIncome	477.957
8	H	2020	Expenses	51.141
8	H	2020	ExpenseRatio	0.097
9	I	2020	Revenue	802.964
9	I	2020	NetIncome	725.351
9	I	2020	Expenses	77.613
9	I	2020	ExpenseRatio	0.097
10	J	2020	Revenue	1,339.922
10	J	2020	NetIncome	1,207.137
10	J	2020	Expenses	132.785
10	J	2020	ExpenseRatio	0.099
11	K	2020	Revenue	506.117
11	K	2020	NetIncome	458.439
11	K	2020	Expenses	47.678
11	K	2020	ExpenseRatio	0.094
12	L	2020	Revenue	823.169
12	L	2020	NetIncome
12	L	2020	Expenses	823.169
12	L	2020	ExpenseRatio	1.000
13	M	2020	Revenue	1,043.384
13	M	2020	NetIncome	939.986
13	M	2020	Expenses	103.398
13	M	2020	ExpenseRatio	0.099
14	N	2020	Revenue	936.808
14	N	2020	NetIncome	846.258
14	N	2020	Expenses	90.550
14	N	2020	ExpenseRatio	0.097
15	O	2020	Revenue	686.492
15	O	2020	NetIncome	618.461
15	O	2020	Expenses	68.031
15	O	2020	ExpenseRatio	0.099
88	X	2020	Revenue	764.669
88	X	2020	NetIncome	689.512
88	X	2020	Expenses	75.157
88	X	2020	ExpenseRatio	0.098
1	A	2021	Revenue	616.833
1	A	2021	NetIncome	555.705
1	A	2021	Expenses	61.128
1	A	2021	ExpenseRatio	0.099
2	B	2021	Revenue	896.129
2	B	2021	NetIncome	812.447
2	B	2021	Expenses	83.682
2	B	2021	ExpenseRatio	0.093
3	C	2021	Revenue	638.339
3	C	2021	NetIncome	577.160
3	C	2021	Expenses	61.179
3	C	2021	ExpenseRatio	0.096
4	D	2021	Revenue	998.222
4	D	2021	NetIncome	899.299
4	D	2021	Expenses	98.923
4	D	2021	ExpenseRatio	0.099
5	E	2021	Revenue	1,987.197
5	E	2021	NetIncome	1,803.264
5	E	2021	Expenses	183.933
5	E	2021	ExpenseRatio	0.093
6	F	2021	Revenue	1,064.687
6	F	2021	NetIncome	963.518
6	F	2021	Expenses	101.169
6	F	2021	ExpenseRatio	0.095
7	G	2021	Revenue	1,435.471
7	G	2021	NetIncome	1,297.894
7	G	2021	Expenses	137.577
7	G	2021	ExpenseRatio	0.096
8	H	2021	Revenue	819.424
8	H	2021	NetIncome	743.579
8	H	2021	Expenses	75.845
8	H	2021	ExpenseRatio	0.093
9	I	2021	Revenue	1,329.483
9	I	2021	NetIncome	1,200.978
9	I	2021	Expenses	128.505
9	I	2021	ExpenseRatio	0.097
10	J	2021	Revenue	990.097
10	J	2021	NetIncome	897.640
10	J	2021	Expenses	92.457
10	J	2021	ExpenseRatio	0.093
11	K	2021	Revenue	2,073.561
11	K	2021	NetIncome	1,874.829
11	K	2021	Expenses	198.732
11	K	2021	ExpenseRatio	0.096
13	M	2021	Revenue	738.247
13	M	2021	NetIncome	669.308
13	M	2021	Expenses	68.939
13	M	2021	ExpenseRatio	0.093
14	N	2021	Revenue	919.003
14	N	2021	NetIncome	833.941
14	N	2021	Expenses	85.062
14	N	2021	ExpenseRatio	0.093
15	O	2021	Revenue	1,840.421
15	O	2021	NetIncome	1,667.048
15	O	2021	Expenses	173.373
15	O	2021	ExpenseRatio	0.094
88	X	2021	Revenue	2,073.931
88	X	2021	NetIncome	1,880.264
88	X	2021	Expenses	193.667
88	X	2021	ExpenseRatio	0.093

[v-jingzhang · Accepted solution]

Hi @user01 

Please use this measure

%Change = AVERAGEX(VALUES(sampdata[FiscalYear]),DIVIDE([ValueM_Adj]-[ValueM],[ValueM]))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[user01]

Desired Results

Year	Item	Value	Value Adj	%Change
2019	Expenses	40.138	50.138	24.91%
2020	Expenses	75.157	85.157	13.31%
2021	Expenses	193.667	203.667	5.16%
	Average	102.987	112.987	14.46%
2019	NetIncome	375.118	366.118	-2.40%
2020	NetIncome	689.512	680.512	-1.31%
2021	NetIncome	1,880.264	1,871.264	-0.48%
	Average	981.631	972.631	-1.39%
2019	ExpRevRatio	0.097	0.121	24.91%
2020	ExpRevRatio	0.098	0.111	13.31%
2021	ExpRevRatio	0.093	0.098	5.16%
	Average	0.096	0.110	14.461%