Sorting portfolios based on criterias

[Myura]

My current table looks like this

Company-----year----size-----Exchange
A---------------2000-----80-------A
A---------------2001-----85-------A
B---------------2002------90------C

I want to allocate the companies into two categories "Big" and "Small".

For a particular year, if the companies size is bigger than the median of the size of the companies in that year in Exchange A, will be called "BIG".
something like this,

=if([size]>MEDIANX(filter(filter(tbl1,[Year]=A),[Year]),[size]),"Big","Small")

I know the way I used the filters are wrong. I do not know how to do this. Please help me.

 

[scottsen]

This should be super close:

=if(tbl1[size] > calculate(MEDIAN(tbl1[size]), FILTER(ALL(tbl1), tbl1[Year] = EARLIER(tbl1[Year]) && tbl1[Exchange] = EARLIER(tbl1[Exchange])), "big", "small")