Sum Commission Tiers- Sum an If Statement?

[TuesdayApril]

Weekly Spread is the sum of 2 different tables spread amounts

I need the sum of commission for each tier, the below doesn't Sum for me:

Commission =
If([Weekly Spread] > [Start Amount],
MIN([Weekly Spread],
[End Amount]) - [Start Amount],
0)* [Commission%]

I tried this, but it is just taking the highest tier and applying that percentage. rather than adding the amounts from each tier below"

 

Total Commission =

VAR SalesAmount = [Timesheet Spread] + [Adjustment Spread]

RETURN

    SWITCH (

        TRUE (),

        SalesAmount >= 11001, 0.12 * SalesAmount,

        SalesAmount >= 7501, 0.08 * SalesAmount,

        SalesAmount >= 5001, 0.04 * SalesAmount,

        SalesAmount >= 0, 0.02 * SalesAmount

    )

How do I get the above to add the commission on each segment?

Thanks in advance.

[v-rzhou-msft]

Hi @TuesdayApril ,

I couldn't find many value in your data model, like [Weekly Spread],[Start Amount],[End Amount]... Also [Timesheet Spread] and [Adjustment Spread] in your calculation.

Please share a sample file with me and show me a screenshot with me. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

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

[TuesdayApril]

I figured it out. I just used variables to sum each tier and then summed the values together. Thanks!