cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Lookup Value

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

3 REPLIES 3
Community Support

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!

Super User

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

Check for more intersing solution here: https://www.youtube.com/@letssolveproblem

Regards

Helper V

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors