Measure using If (or switch) pulling from two tables

[Anonymous]

I have a business scenario whereby we need to assign different total rebate dollars to a customer, based on hitting minimum thresholds  (shipped revenue).    The idea being that, depending on where their shipped revenue value is within the ranges, they get the Rebate %  * the shipped revenue.    In the case where they are less than < 1000  they would get .05%.    From 1000 to 4999, they would get 1%.    5000 to 9999 they would get 2%, etc.   

I am trying to use IF or SWITCH in a measure, but DAX won't allow me to use just the column names unless I put a MAX, MIN, etc in front of the column name.   Any guidance on how to set this up would be great.  

Rebate Table

Customer	Minimum Revenue	Rebate %
Customer A	1000	.01
Customer A	5000	.02
Customer A	10000	.03

So, assuming these were the values in the shipping table, then in January the customer would earn a rebate of $1.875   (375 * .005).     In February, they met the $10k threshold, so it would be the shipped revenue * 3%.    

Shipments Table

Month	Customer	Shipped Revenue
Jan	Customer A	375
Feb	Customer A	11500
Mar	Customer A	6000

[v-yalanwu-msft · Accepted solution]

Hi @Anonymous  , 

As Amitchandak said ,You could add another measure to modify the total value based on the original measure:

_Value =
SUM ( Shipments[Shipped Revenue] )
    * MAXX (
        FILTER ( Rebate, MAX ( Shipments[Shipped Revenue] ) > [Minimum Revenue] ),
        [Rebate %])

Another measure:

Internal Use % =
VAR _new =
    SUMMARIZE ( 'Shipments', [Customer], [Month], "_total", [_Value] )
RETURN
    IF ( HASONEVALUE ( 'Shipments'[Customer] ), SUMX ( _new, [_total] ) )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[amitchandak]

@Anonymous , try two meaures like

M1= sum(Shipments[Shipped Revenue]) * maxx(filter(Rebate , Rebate[Customer] = max(Shipments[Customer]) && sum(Shipments[Shipped Revenue]) >Customer[Minimum Revenue]),[Rebate %])

M1 should work with Shipments[Customer],Shipments[Month]

or try like

rebate amount = sumx(summarize(Shipments, Shipments[Customer],Shipments[Month], "_1",[M1]),[_1])

[Anonymous]

Hi @amitchandak      It is bascially working, thank you, except for the total that I get: 

[Anonymous]

Hi @amitchandak     I see what the formula is doing now, and where the "error" is with the sum.   The measure actually works fine at the row level.    But because my last threshold in my data is $1,000,000 - the calcuation is using that sum total * by the max % in the table.   In this case, $2,391,531  *  3.5% which is giving me the incorrect total.   I am really looking for the total of the rows shown in the table (starting with $30,405).