Conditional Aggregate within Matrix

[Anonymous]

I'd like to create a calculation within a matrix similar the following. Rows are Cohort, columns are Year and values in the matrix cells are based on the following logic: if Year == 2020 then average(Value 3) else sum(Value 1) / sum(Value 2) end

My data looks similar to the following.

Cohort	Year	Value 1	Value 2	Value 3
2016	2016	0.82143	0.30616	0.791628
2107	2107	0.547136	0.375904	0.088427
2018	2018	0.566549	0.846655	0.263815
2019	2019	0.091474	0.603061	0.371852
2016	2016	0.478472	0.327595	0.351641
2107	2107	0.602757	0.422631	0.773349
2018	2018	0.954778	0.722917	0.079683
2019	2019	0.020741	0.942776	0.908014
2016	2020	0.791773	0.678128	0.727963
2107	2020	0.83448	0.551655	0.873079

[Greg_Deckler]

Sounds like you need a measure.

[Anonymous]

Measures seems to disagree as near as I can tell.. When I try entering a formula similar to: if(year = 2020, average(value 3), sum(value 1) / sum(value 2)) the formula bar wont even let me select the year column.

[parry2k · Accepted solution]

@Anonymous as my friend @Greg_Deckler recommend, it has to be measure, if you are use Year , you have to provide aggregation for year

if(MAX( year ) = 2020, average(value 3), sum(value 1) / sum(value 2))