Forum Discussion

CJ_96601's avatar
CJ_96601
Helper V
2 years ago

Lookup Value

Below table refers:

 

 

Company Year 1Year 2Year 3
Option 13.00%3.50%4.50%
Option 25.00%6.50%7.00%
Option 34.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

Need Help

3 Replies

  • qqqqqwwwweeerrr's avatar
    qqqqqwwwweeerrr
    Solution Sage
    2 years ago

    Hi CJ_96601 

     

    if this is your input table:

    Company Year 1Year 2Year 3
    Option 13.00%3.50%4.50%
    Option 25.00%6.50%7.00%
    Option 34.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's avatar
      CJ_96601
      Helper V
      2 years ago

      Good evening, 

       

      I will give another table, maybe my example and requirements are not clear.

       

      DS Table

      YearBUKPIItemData
      1/1/20243 G6.00%
      1/1/20241ThresholdG3.00%
      1/1/20242ThresholdG5.00%
      1/1/20243ThresholdG4.00%
      1/1/20244ThresholdH24.00%
      1/1/20245ThresholdH38.00%
      1/1/20241StretchH57.00%
      1/1/20242StretchH11.00%
      1/1/20243StretchB18.00%
      1/1/20244StretchB37.00%
      1/1/20245StretchB32.00%
      1/1/20241TargetB49.00%
      1/1/20242TargetA16.00%
      1/1/20243TargetA8.00%
      1/1/20244TargetA12.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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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