Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bflaviu
Regular 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
Anonymous
Not applicable

Seems to me like you just need use

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

 


@Anonymous 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.

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.