Tiered Measure for Rebate Calculation

[jeck12]

I'm developing a report that pulls together revenue data and then calculates the rebate amount, based on specific parameters, which is different for each individual customer. There are simple calculations, which are just straight %'s of revenue, but there are also tiered scenarios. 

Below is the data that I'm looking at currently. What I've got so far is a flag in the Rebate Methodology column that's "S" if it's a simple calculation, which can just be revenue*rebate%. If the flag is "T", then it's a tiered rebate. How would I go about writing a DAX Measure that accomplishes calculating the Rebate amount, performing the simple calculation above if the flag is "S", but calculating based on tiers if the flag is "T"? Based on the different tiers for each of these different customers. The tiers are different for each customer, they aren't the same across the board.

Customer Name	Revenue	Rebate Methodology	Percent	General Rebate / GPO terms
A	1000000	S	45.00%	Proceeds and Customer owned  inventory sales 97.5%
B	4000000	T	Tiered from 1% to 3.5%	1% first $1.5M, 1.5% next $0.5M, 2% next $0.5M, 2.5% next $0.5M, 3% next $0.5M, 3.5% excess over $3.5M
C	4000000	T	Tiered from 1% to 3.5%	1% first $1.5M, 1.5% next $0.5M, 2% next $0.5M, 2.5% next $0.5M, 3% next $0.5M, 3.5% excess over $3.5M
D	1000000	S	1.00%	No current rebate program, holding a 2020 and 2021 rebate
E	40000000	T	Tiered from 2% to 6%	2% volume rebate on $20M or less, 3.5% on $20 to $25M, 4.5% on $25 to $30M, 5.5% on $30 to $35M, 6% greater than $35M
F	40000000	T	Tiered from 2% to 6%	2% volume rebate on $20M or less, 3.5% on $20 to $25M, 4.5% on $25 to $30M, 5.5% on $30 to $35M, 6% greater than $35M
G	2000000	T	Tiered from 1% to 3%	Rebate paid after attaining $650,000 in sales. 1.0% volume rebate on $650K to $800K, 2.0% on $800K to $1M, 3.0% on $1M and above

[Anonymous · Accepted solution]

Hi @jeck12 

My assumption is that the stratification is based on the value of Revenue.

Here is a simple program I offer that you can reference.

The point is that after determining the Rebate Methodology, a hierarchical calculation is performed based on Revenue.

Create a measure.

Measure = 
VAR _RebateMethodology = SELECTEDVALUE('Table'[Rebate Methodology])
VAR _Revenue = SELECTEDVALUE('Table'[Revenue])
RETURN
SWITCH(
    _RebateMethodology,
    "S", _Revenue * 0.5,
    "T", 
    IF(
        _Revenue < 4000000,
        _Revenue * 0.1,
        IF(
            _Revenue >= 4000000 && _Revenue < 40000000,
            _Revenue * 0.2,
            _Revenue * 0.3        
        )
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.