Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi
Can you try with Distinct values like below
If (Year = 3, Sum({$<Year={'1', '2', '3'}>} Distinct Rate))
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% |