Forum Discussion
DAX Help for Sales Commission Calculation
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
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?
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
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