I need to do something that seems simple, but I can't seem to figure it out. I want to create a measure that compares some aggregates to a single value from another table. For example, I have two tables: Orders and CSR Goals. The Orders table has a Order Value column and a CSR column. The CSR Goals has only two columns: CSR and Goal. They are tied on CSR. Now, I want a simple measure that gives me a Percent(%) to Sales Goal by using the SUM('Orders'[OrderValue])/<GOAL>, where <GOAL> is the value in CSR Goals in the current context.
Another way to think of it is I need a KPI that tells me if they sold above or below their goal. I can do everything up to the point where I need it to compare to the goal. I have 5 sales people, and each one has an arbritray goal value, which I have in a table. I need to measure to use that value in the calculation.
In the example in the images below, I showed "conceptually" what I need my calculation to do. I"m starting to feel crazy because I can write a measure that simply divides a sum by a single number like SUM(Values)/5. All I'm asking is instead of me writing "5", I need that single number to be pulled from a table that says that number should be "5".
Technically that would work in this simple scenario. But the concept still eludes me for when I need something a bit more complex. I was trying to post the most basic example to try to understand the concept of pulling a single value from a similar table. I'm actually facing a situation where some of the CSR goals are hard-coded like in this example, but others have another measure, like SUM of sales of their customers last year plus 10%. So my Goals table might have a column like "Needs Override" that's true or false (1,0) and the "Override Goal". So the measure would be more like:
IF('CSR Goals'[NeedsOverride]=1,'CSR'[Goal],[Sales Last Year])
Where [Sales Last Year] is already the main goal measure.