Rank with a dimention that is not in the chart

[#1]

Hi,

I have a table Like This:

Company	Year	Ratio	Value
CEUJ	2011	Ratio1	5593525
CEUJ	2012	Ratio1	5367548
CEUJ	2013	Ratio1	5544482
CEUJ	2014	Ratio1	4875582
CEUJ	2015	Ratio1	1714342
CEUJ	2011	Ratio2	2494074
CEUJ	2012	Ratio2	1661454
CEUJ	2013	Ratio2	8148792
CEUJ	2014	Ratio2	8200381
CEUJ	2015	Ratio2	8190812
DOGD	2011	Ratio1	3451768
DOGD	2012	Ratio1	2334441
DOGD	2013	Ratio1	6271079
DOGD	2014	Ratio1	4344662
DOGD	2015	Ratio1	8181262
DOGD	2011	Ratio2	5350576
DOGD	2012	Ratio2	1177257
DOGD	2013	Ratio2	5170544
DOGD	2014	Ratio2	6574504
DOGD	2015	Ratio2	6693989

then I have a Chart with Year & Ratio as Dimension & Value for One Company as Expression.

Now, What I want is to show rank of Company in given Year & Ratio as Expression in Chart described above.

I prefer not to change dimensions and to have just one expression. since it affects chart coloring.

Any Idea on How to accomplish this??

Thanks in Advance

BTW I have attached my sample file.

[#2]

Here is the expression I used.

Note that I used 'Only' because I haven't filtered the data with company. instead I have a second field called selected company that determines which company is supposed to be shown.

only({<Company={$(=SelectedCompany)}>}aggr(rank(sum(aggr(sum(Value),Ratio,Year,Company))),Ratio,Year,Company))

if I was filtering data with company the formula would be like this:

Aggr(Rank(Sum({<Company>} Aggr(Sum({<Company>} Value),Ratio,Year,Company))),Ratio,Year,Company)

[#3]

BTW Could you explain what the expression does for further development??

[Digvijay_Singh]

Thanks for sharing!