Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've got two tables, Sales and Targets, whose data I want to present in a report. On the report I have a Line Chart presenting Sales by Territory. I also have a Card with the Quarter Target. Initially, this Card should show the overall Target (the one with the NULL Territory). Once the user selects (via legend or line) a highlight on Territory, the Card should change to show the Territory Target.
I'm relatively new to Power BI. I'm familiar with the use of ALL and ALLEXCEPT. I'm not clear on how I would achieve what I'm looking for above. Any thoughts?
Thanks.
Joseph
Sales | ||
Territory | Sales | YoY% |
MidWest | 1500 | 12.5 |
Eastern | 2000 | 10.4 |
Pacific | 3000 | 8.8 |
Targets | ||
Quarter | Territory | Target (YoY%) |
Q1FY20 | MidWest | 15 |
Q1FY20 | Eastern | 10 |
Q1FY20 | Pacific | 14 |
Q1FY20 | 13.5 |
Solved! Go to Solution.
Perhaps:
Measure =
VAR __Territory = MAX('Sales'[Territory])
RETURN
IF(HASONEVALUE('Sales'[Territory]),
LOOKUPVALUE('Targets'[Target YoY%],'Targets'[Territory],__Territory]),
MAXX(FILTER('Targets',ISBLANK('Targets'[Territory])),'Targets'[Target YoY%])
)
Perhaps:
Measure =
VAR __Territory = MAX('Sales'[Territory])
RETURN
IF(HASONEVALUE('Sales'[Territory]),
LOOKUPVALUE('Targets'[Target YoY%],'Targets'[Territory],__Territory]),
MAXX(FILTER('Targets',ISBLANK('Targets'[Territory])),'Targets'[Target YoY%])
)
Thanks for responding. I'm working on understanding and implementing your solution. I'll let you know how it goes.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.