Forum Discussion

JanineG's avatar
JanineG
Frequent Visitor
6 years ago

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

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)))

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      JanineG
      Frequent Visitor
      6 years ago

      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!!