Lookup Value

[CJ_96601]

Below table refers:

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%

How to create measure (lookup value) like in excel where if Year 1 and Option 1 will yield 3%

Year 2 and Option 2 = 6.5%

Year 2 Option 1 = 3.5%

Thanks

[v-yaningy-msft]

Hi, @CJ_96601 

Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Convert the chart format to this:

Excel-like visual effects can be realized using a matrix:

The final result:

Measure:

getValue =

VAR SelectedYear =

    SELECTEDVALUE ( 'Year'[year] )

VAR SelectedCompany =

    SELECTEDVALUE ( 'company'[company] )

RETURN

    CALCULATE (

        SUM ( 'Table'[Value] ),

        'Table'[Year] = SelectedYear

            && 'Table'[Company] = SelectedCompany

    )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[qqqqqwwwweeerrr]

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

[CJ_96601]

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