Measure that selects correct discount based on varying quantities from two tables

[JanineG]

Hi there, 

I desperately need help. 
I try to calculate the scale discount for my price waterfall visual. Yet, the scale discount EU data table gives me three different scale discounts (in three seperate columns) for three respectively different billing quantities per material ID and transaction. 

In my transaction table also having the material IDs, I have the billing quanitites for transaction. 

If in my price waterfall I now filter for  customer xx, it will show me only the billed quanities per material number for this customer. Yet, I dont succeed to get the scale discount correct (depending on the billed quantities). I tried as follows:

Can someone please help me out? I am so lost... Many thanks!! Janine

Scale Discount in % = if(SELECTEDVALUE('Transaction Data'[Billing Qty:SlsUnits])<'Scale discounts EU'[Order Qty Scale D. 2],'Scale discounts EU'[Scale Discount-1],if(selectedvalue('Transaction Data'[Billing Qty:SlsUnits])<'Scale discounts EU'[Order Qty Scale D. 3], 'Scale discounts EU'[Scale Discount-2], if(selectedvalue('Transaction Data'[Billing Qty:SlsUnits])>'Scale discounts EU'[Order Qty Scale D. 3],'Scale discounts EU'[Scale Discount-3],0)))

[amitchandak]

@JanineG 

The information you have provided is not making the problem clear to me. Can you please explain with an example. Can you share sample data and sample output.

Appreciate your Kudos.

[JanineG]

Thanks a lot, 

This is the visual I want to build with the right picture showing the table where I can find the scale discounts based on the quantities ordered. In another data table - my order transaction data - , I have again the Material ID (this ID is also used for the relationship) for many many parts (more than the ones eligible for a scale discount) with a column called "billing qty per month" for this respective Material ID. 

What I now want to get es a measure is: based on the actually orders / transactions: what scale discount was granted on average for the ordered material ID's of the transaction table considering that each material ID comes with a different (or no) scale discount for respective order quantities 

Does that help @amitchandak ? Thanks again!!

[Greg_Deckler]

Sample data, expected output, logic to get from a to b. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490