topic Re: Dax for Bonus Threshold in DAX Commands and Tips

Dax for Bonus Threshold

Tarek78 — Thu, 22 Feb 2024 22:50:07 GMT

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 ?

Re: Dax for Bonus Threshold

lbendlin — Fri, 23 Feb 2024 01:32:12 GMT

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

Re: Dax for Bonus Threshold

Anonymous — Fri, 23 Feb 2024 06:29:25 GMT

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.

Re: Dax for Bonus Threshold

Tarek78 — Fri, 23 Feb 2024 11:16:14 GMT

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.

Re: Dax for Bonus Threshold

Tarek78 — Fri, 23 Feb 2024 11:18:48 GMT

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 ?