DAX Help for Sales Commission Calculation

[BH22One]

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 however if the salesman reaches their goal for that quarter, multiply the Net Margin Sales Amount (After the Goal has been met) * Bonus Commission Rate.

For example, Joe as a Goal of $1000 for the Quarter. In mid Feb, Joe reaches his goal. From Mid Feb to end of March whatever sales he makes between that time will be multipled against the bonus rate.

Where I am running into trouble is I can not figure out a way to have Power BI use the Bonus Rate and ignoring any Net Margin Sales Amounts prior to the timefram where Joe hit his goal.

https://www.dropbox.com/s/rqxxgxsukqoyhj1/Commissions%20Sample.pbix?dl=0

I've include a link to some sample data that mimics this report. The actual report is more complex but if I can solve for this requirement I'll be well on my way.

Thanks,

B

[sturlaws]

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]

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]))

[BH22One]

Hi @sturlaws 

Many, many thanks to your solution. After testing this with my current data set, I'm not sure this would 100% solve for the requirement we have after giving this some more thought. The Sample data used was too ideal of an example. In reality, there are daily records of the "Net Sales AMT" in the data in stead of monthly. Based on the measure below, Power BI wouldn't use the second commission rate granted if that salesman met their goal on a specific date.

For example, Salesman A has a goal for Q1 of $1000. By 2/15/2020, Salesman A has sold a accummulative total of $995. On the next day (2/16/2020), Salesman A reaches a cummulative total of $1050 which means they would've sold $95 on 2/16/2020. Ideally, $5 would be associated with one commission rate and $90 would be associated with the second commission rate since Salesman A would've hit their goal.

Is there a way to refine the measure or even calculated column to do such? 

[sturlaws · Accepted solution]

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

[BH22One]

@sturlaws I was able to use the second solution you sent over. It looks to work very well in the application we needed it for. I appreciate your assistance!

-B

[BH22One]

@sturlaws 

I appreciate your continued help. I will attempt to use the calculated column solution you have presented. I have also attached a better copy of the sample data. The file can be found here https://www.dropbox.com/s/ag045tthqnntlng/Commissions%20SampleV2.pbix?dl=0

I added a calendar table and have a Sample FACT table that resembles the information I have.

Thanks,

-B