Forum Discussion

BH22One's avatar
BH22One
Icon for Helper I rankHelper I
6 years ago
Solved

DAX Help for Sales Commission Calculation

Good Afternoon,   I am struggling to discover a way to perform a requirement our business sales team has for it's commission report.   Requirement multiply Net Margin Sales * Commission Rate howe...
  • sturlaws's avatar
    sturlaws
    6 years ago

    A calculated column would look something like this:

    c =
VAR _salesperson =
    CALCULATE ( SELECTEDVALUE ( 'Table'[salesperson] ) )
VAR _quater =
    CALCULATE ( SELECTEDVALUE ( 'Table'[yearQuaterNum] ) )
VAR _date =
    CALCULATE ( SELECTEDVALUE ( 'Table'[date] ) )
VAR _salesQTD =
    CALCULATE (
        SUM ( 'table'[sales] ),
        FILTER (
            ALL ( 'table' ),
            'table'[salesperson] = _salesperson
                && 'table'[yearQuaterNum] = _quater
                && 'table'[Date] <= _date
        )
    )
RETURN
    IF ( _salesQTD < 'table'[target], rate1, rate2 )

     

    If you provide a relevant dataset, I can demonstrate how to do this as a measure as well

sturlaws's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Hi BH22One 

 

you can create a measure like this:

Comission rate = 
SUMX (
    ADDCOLUMNS (
        ADDCOLUMNS (
            VALUES ( 'Sample Data'[COMMISSION_DATE] ),
            "Net", CALCULATE(
                    SUM ( 'Sample Data'[Net Margin AMT] ),
                    filter(ALL('Sample Data'),'Sample Data'[COMMISSION_YEAR]=max('Sample Data'[COMMISSION_YEAR]) &&
                    'Sample Data'[YearQuaterInt]=max('Sample Data'[YearQuaterInt]) &&
                    'Sample Data'[COMMISSION_MONTH]<=max('Sample Data'[COMMISSION_MONTH]))
                )         ,
            "Target", CALCULATE ( MAX ( 'Sample Data'[QTR GOAL] ) )
        ),
        "Commision rate", IF (
            [Net] > [Target],
            CALCULATE ( SELECTEDVALUE ( 'Sample Data'[Tier 2B Rate] ) ),
            CALCULATE ( SELECTEDVALUE ( 'Sample Data'[Tier 1B Rate] ) )
        )
    ),
    [Commision rate]
)

 

This measure uses the ALL-function around the table 'Sample data'. Hopefully you have a date table, so it should be changed to ALL(DateTable). I also created a new column, YearQuaterInt, which should reside in your date table.

 

This solution is a measure. An alternative would be to create a calculated column.

 

Cheers,
Sturla

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks, I've been trying for hours to sum my comission measure and your idea helped me solve it.

In my case what solved it was that I created a "July Payout" measure that got things correct for the individual, but not the sum. With your idea I then added the following, and now it works:

 

SUMX(ADDCOLUMNS(Compo,"Name",ALLSELECTED(Compo[Full_Name]),"Comission",[July_Payout]),MAX([Comission]))