Dax for Bonus Threshold

[Tarek78]

Hi All

We give our customers a bonus based on various conditions. Those conditions are different per customer but the same logic. 

example: 

customer 1: if revenue < 1M€, no bonus. Between 1M and 2M, 5% of sales as bonus, above 2M, 7% of bonus

customer 2: if revenue < 2M€, no bonus. Between 2M and 5M, 7% of sales as bonus, above 5M, 8% of bonus

1% of sales on product A is more than 1 M

2% on the growth (revenue current year - revenue last year) if growth is 10%, 3% if the growth is between 10 and 15%

I am looking at a way to build a DAx formula and why not a data model. 

do you have any idea ?

[v-zhouwen-msft]

@lbendlin Thanks for the quick reply, I assumed some data.

@Tarek78

 The table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure named ‘Product A Sales Bonus’

Product A Sales Bonus =
VAR _a =
    DIVIDE (
        SUMX (
            'Tabelle1',
            'Tabelle1'[Sales of product A this year] - 'Tabelle1'[Sales of product A last year]
        ),
        SUMX ( 'Tabelle1', 'Tabelle1'[Sales of product A last year] ),
        0
    ) * 100
VAR _c =
    SUMX (
        'Tabelle1',
        [Sales of product A this year] - [Sales of product A last year]
    )
VAR _b =
    IF (
        SUM ( Tabelle1[Sales of product A this year] ) >= 1000000,
        SWITCH (
            TRUE (),
            _a < 10, SUM ( Tabelle1[Sales of product A this year] ) * 0.01,
            _a = 10,
                SUM ( Tabelle1[Sales of product A this year] ) * 0.01 + _c * 0.02,
            _a > 10
                && _a <= 15,
                SUM ( Tabelle1[Sales of product A this year] ) * 0.01 + _c * 0.03
        ),
        SWITCH (
            TRUE (),
            _a < 10, 0,
            _a = 10, _c * 0.02,
            _a > 10
                && _a <= 15, _c * 0.03
        )
    )
RETURN
    _b

2. Use the following DAX expression to create a measure named ‘Total Revenue Bonus’

Total Revenue Bonus =
IF (
    EXACT ( MAXX ( 'Tabelle1', [Type] ), "customer1" ),
    SWITCH (
        TRUE (),
        SUM ( Tabelle1[Total revenue] ) < 1000000, 0,
        SUM ( Tabelle1[Total revenue] ) >= 1000000
            && SUM ( Tabelle1[Total revenue] ) < 2000000, SUM ( Tabelle1[Total revenue] ) * 0.05,
        SUM ( Tabelle1[Total revenue] ) > 2000000, SUM ( Tabelle1[Total revenue] ) * 0.07
    ),
    SWITCH (
        TRUE (),
        SUM ( Tabelle1[Total revenue] ) < 2000000, 0,
        SUM ( Tabelle1[Total revenue] ) >= 2000000
            && SUM ( Tabelle1[Total revenue] ) < 5000000, SUM ( Tabelle1[Total revenue] ) * 0.07,
        SUM ( Tabelle1[Total revenue] ) > 5000000, SUM ( Tabelle1[Total revenue] ) * 0.08
    )
)

3. Use the following DAX expression to create a measure named ‘Total Bonus’

Total Bonus = [Product A Sales Bonus] + [Total Revenue Bonus]

4. Final output

Best Regards,

Wenbin Zhou

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

[Tarek78]

Thanks so much for your help, I will try to understand and follow your formula.

my preferred option would be to create a data model. Do you have knowledge on this? Maybe I could send the details in excel and we could find together the best data model to create ?

[lbendlin]

That's a standard prorated pattern.  Why not a data model, indeed?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Tarek78]

Thanks for your reply. I agree, the easiest and my preferred option would be to create a data model where we will use excel files to compile all the different bonuses and thresholds. 

However I'm not sure how to build this.