Forum Discussion

Kosh's avatar
Kosh
Frequent Visitor
2 years ago

Calculate Commission from a Range table

I want to calculate commission for each employee from a range table (pls. refer below). Example - If Person A's total sales amount is 1500, then the measure should give output of 80 ( 5% for 1000 = 50 and 6% for remaining 500 = 30).

 

Thanks

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Kosh ,
    Based on your description, you want to calculate commissions for different personnel grades, you can try the following steps. There is no total sales in the data you provided, so we have to calculate the total sales first, if you have total sales you can just use the second MEASURE

    Total Sales = 
VAR _MaxUpto = 
CALCULATE(
    MAX('Table'[Upto]),
    ALLEXCEPT(
        'Table',
        'Table'[Person Name]
    )
)
VAR _SumUpto = 
CALCULATE(
    SUM('Table'[Upto]),
    ALLEXCEPT(
        'Table',
        'Table'[Person Name]
    )
)
VAR _MinGreaterThan = 
CALCULATE(
    MIN('Table'[Greater Than]),
    ALLEXCEPT(
        'Table',
        'Table'[Person Name]
    )
)
VAR _SumGreaterThan = 
CALCULATE(
    SUM('Table'[Greater Than]),
    ALLEXCEPT(
        'Table',
        'Table'[Person Name]
    )
)
RETURN
(_SumUpto + _SumGreaterThan - _MaxUpto - _MinGreaterThan)/2 + _MaxUpto + _MinGreaterThan
    Total Commison = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[Person Name]) = "Person A",
    IF(
        [Total Sales] <= 3000,
        [Total Sales] * 0.07,
        IF(
            [Total Sales] <= 5000,
            3000 * 0.07 + ([Total Sales] - 3000) * 0.06,
            3000 * 0.07 + 2000 * 0.06 + ([Total Sales] - 5000) * 0.05
        )
    ),
    SELECTEDVALUE('Table'[Person Name]) = "Person B",
    IF(
        [Total Sales] <= 10000,
        [Total Sales] * 0.07,
        IF(
            [Total Sales] <= 8000,
            10000 * 0.07 + ([Total Sales] - 10000) * 0.05,
            10000 * 0.07 + 8000 * 0.05 + ([Total Sales] - 18000) * 0.03
        )
    )
)

    Final output

    Best regards,
    Albert He

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

  • Kosh's avatar
    Kosh
    Frequent Visitor
    2 years ago

    Thank you for a quick response. Sorry I forgot to give complete details. Below is a screenshot of the data model.

    tblTargets includes Targets range and commission for each sales person by financial year.

    tblBase contains monthly sales amount by sales person.

    Sales person list changes very often with new person getting added. tblSales also gets updated with every month with sales data for the month.