Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |