cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bflaviu Frequent Visitor
Frequent Visitor

Using a Single Value for Part of a Measure Based on Lookup

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".

 

Example Tables

Example Measure

3 REPLIES 3
Ross73312 Super Contributor
Super Contributor

Re: Using a Single Value for Part of a Measure Based on Lookup

Seems to me like you just need use

DIVIDE(
	SUM('Orders'[OrderValue]),
	SUM('CSR Goals'[Goal])
)

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


bflaviu Frequent Visitor
Frequent Visitor

Re: Using a Single Value for Part of a Measure Based on Lookup


@Ross73312 wrote:

Seems to me like you just need use

DIVIDE(
	SUM('Orders'[OrderValue]),
	SUM('CSR Goals'[Goal])
)

 


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:

 

DIVIDE(
	SUM('Orders'[OrderValue]),
	IF('CSR Goals'[NeedsOverride]=1,'CSR'[Goal],[Sales Last Year])
)

Where [Sales Last Year] is already the main goal measure.

Ross73312 Super Contributor
Super Contributor

Re: Using a Single Value for Part of a Measure Based on Lookup

In that case, if certain goals are calculated, would it be more sensible to forward load that calculation by calculating the goals ahead of time and storing it in a table.

 

This could be making a new table that either draws through the existing value, or if the existing value is flagged, it calculates a new table.

 

If your data has multiple financial years, where you need the goals for each year, you could also assign financial years to this new goal table.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 56 members 1,382 guests
Please welcome our newest community members: