Forum Discussion
Lookup Value
Hi CJ_96601
if this is your input table:
| Company | Year 1 | Year 2 | Year 3 |
| Option 1 | 3.00% | 3.50% | 4.50% |
| Option 2 | 5.00% | 6.50% | 7.00% |
| Option 3 | 4.00% | 4.50% | 5.50% |
then just unpivot table and you desired view can be achived
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: https://www.youtube.com/@letssolveproblem
Regards
Good evening,
I will give another table, maybe my example and requirements are not clear.
DS Table
| Year | BU | KPI | Item | Data |
| 1/1/2024 | 3 | G | 6.00% | |
| 1/1/2024 | 1 | Threshold | G | 3.00% |
| 1/1/2024 | 2 | Threshold | G | 5.00% |
| 1/1/2024 | 3 | Threshold | G | 4.00% |
| 1/1/2024 | 4 | Threshold | H | 24.00% |
| 1/1/2024 | 5 | Threshold | H | 38.00% |
| 1/1/2024 | 1 | Stretch | H | 57.00% |
| 1/1/2024 | 2 | Stretch | H | 11.00% |
| 1/1/2024 | 3 | Stretch | B | 18.00% |
| 1/1/2024 | 4 | Stretch | B | 37.00% |
| 1/1/2024 | 5 | Stretch | B | 32.00% |
| 1/1/2024 | 1 | Target | B | 49.00% |
| 1/1/2024 | 2 | Target | A | 16.00% |
| 1/1/2024 | 3 | Target | A | 8.00% |
| 1/1/2024 | 4 | Target | A | 12.00% |
Syntax,
Sample # 1
If "Year" (from DS Table) is equal to year column (calendar - assuming there is a calendar table and selected year is 2024
If "BU" (from DS Table) is equal to BU column (assuming there is a BU table, let say "3" is selected
Filter "KPI" from DS Table) = " "
Filter "Item" from DS Table = "B"
Result must be 18%
-------------------------------------------------
sample # 2
If "Year" (from DS Table) is equal to year column (calendar - assuming there is a calendar table and selected year is 2024
If "BU" (from DS Table) is equal to BU column (assuming there is a BU table, let say "3" is selected
Filter "KPI" from DS Table) = "Stretch"
Filter "Item" from DS Table = "G"
Result must be 6%
Regards,
Obet