Forum Discussion

Tarek78's avatar
Tarek78
Helper I
2 years ago

Dax for Bonus Threshold

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 ?

Data model
DAX

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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-Forum/ba-p/963216

    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/1447523

    • Tarek78's avatar
      Tarek78
      Helper I
      2 years ago

      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.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Tarek78
      Helper I
      2 years ago

      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 ?