Tiered Measure to Calculate Rebate

[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 a screenshot of what 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.

[Anonymous · Accepted solution]

Hi, @jeck12 

Thanks for @Ritaf1983 reply.  Based on your description, the best thing to do is to create your own rebate rules for each individual. You can refer to the following method.

Measure:

Method A/T = 
VAR _value =
    SELECTEDVALUE ( 'Value'[Value] )
VAR _result =
    IF (
        _value < 1500000,
        _value * 0.01,
        IF (
            _value > 1500000
                && _value <= 2000000,
            ( _value - 1500000 ) * 0.015 + 1500000 * 0.01,
            IF (
                _value > 2000000
                    && _value <= 2500000,
                ( _value - 2000000 ) * 0.02 + 500000 * 0.015 + 1500000 * 0.01,
                IF (
                    _value > 2500000
                        && _value <= 3000000,
                    ( _value - 2500000 ) * 0.025 + 500000 * 0.02 + 500000 * 0.015 + 1500000 * 0.01,
                    IF (
                        _value > 3000000
                            && _value <= 3500000,
                        ( _value - 3000000 ) * 0.03 + 500000 * 0.025 + 500000 * 0.02 + 500000 * 0.015 + 1500000 * 0.01,
                        IF (
                            _value > 3500000,
                            ( _value - 3500000 ) * 0.035 + 500000 * 0.03 + 500000 * 0.025 + 500000 * 0.02 + 500000 * 0.015 + 1500000 * 0.01
                        )
                    )
                )
            )
        )
    )
RETURN
    _result

Method B/T = 
VAR _value =
    SELECTEDVALUE ( 'Value'[Value] )
VAR _result =
    IF (
        _value < 20000000,
        _value * 0.02,
        IF (
            _value > 20000000
                && _value <= 25000000,
            _value * 0.035,
            IF (
                _value > 25000000
                    && _value <= 30000000,
                _value * 0.045,
                IF (
                    _value > 30000000
                        && _value <= 35000000,
                    _value * 0.055,
                    IF ( _value > 35000000, _value * 0.06 )
                )
            )
        )
    )
RETURN
    _result

S = 
VAR _value =
    SELECTEDVALUE ( 'Value'[Value] )
VAR _result = _value * 0.015
RETURN
    _result

True Rebate = 
VAR _rebateMethod =
    SELECTEDVALUE ( 'Rebate Methods'[Rebate Methodology] )
VAR _customer =
    SELECTEDVALUE ( 'Rebate Methods'[Customer] )
VAR _result =
    SWITCH (
        _rebateMethod,
        "T", SWITCH ( _customer, "A", [Method A/T], "B", [Method B/T] ),
        "S", [S]
    )
RETURN
    _result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Ritaf1983]

Hi @jeck12 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523