How to get sum of previous rate and current year rate?

[su_pyae]

Hi everyone, 

I have data similar to the following table:

Year	Cost	Rate	Desired Rate
4	100	45%	-
4	200	45%	-
3	50	30%	Year 1 Rate + Year 2 Rate + Year 3 Rate
3	100	30%	Year 1 Rate + Year 2 Rate + Year 3 Rate
2	20	22%	Year 1 Rate + Year 2 Rate
1	150	10%	Year 1 Rate

The data can be 1 row or 2 rows per Year based on the situation. 

To get the "Desired Rate", I was using set analysis with if statement: If (Year = 3, Sum({$<Year={'1', '2', '3'}>} Rate))to get the rate for Year 3. With this set analysis, I want 62% for Year 3, but I am getting 92% (extra 30% coming from Year 3 rate).

For Year 3, I am getting 60% which is the double of actual rate (30%) since we have 2 rows for Year 3. 

Thank you so much for your help in advance. 

[MayilVahanan]

Hi

Can you try with Distinct values like below

 If (Year = 3, Sum({$<Year={'1', '2', '3'}>} Distinct Rate))

[su_pyae]

Hi Mayil, 

When I used Distinct like you have mentioned, it only give me Year 3 Rate once without summing rates from other years. 

Year	Rate	After using Distinct
4	45%	45%
3	30%	30%
2	22%	22%
1	10%	10%