The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Need help to add a calculated column for sales commission in each row of the Sales table. The commission % is determined by the total YTD revenue for the sales person for which the lookup range is in Target table.
Example - For Person1
18-Dec-2024 - YTD invoice is 500, so commission @ 5%
13-Jan-2025 - YTD invoice is 2000 (Dec - 500+Jan - 1500), first 500 already paid in Dec, remaining 500 @5% and 1000 @ 6%.
24-Feb-2025 - YTD invoice is 4000 (Dec 500 + Jan - 1500 + Feb 2000) first 2000 already paid, remaining 1500 @ 6% and 500 @ 7%
Total = 235
Solved! Go to Solution.
First, create a calculated column in the Sales table to calculate the Year-To-Date (YTD) revenue for each sales person.
YTD Revenue =
CALCULATE(
SUM(Sales[Invoice Amount]),
FILTER(
Sales,
Sales[Sales Person] = EARLIER(Sales[Sales Person]) &&
Sales[Invoice Date] <= EARLIER(Sales[Invoice Date])
)
)
Next, create a calculated column to determine the commission rate based on the YTD revenue and the Target table.
Create a Calculated Column for Commission Amount: Finally, create a calculated column to calculate the commission amount for each row.
Commission Amount =
Sales[Invoice Amount] * Sales[Commission Rate]
Combine the Results: You can now combine these columns to get the total commission for each sales person.
Total Commission =
SUMX(
Sales,
Sales[Commission Amount]
)
By following these steps, you will be able to calculate the commission for each row in the Sales table based on the YTD
Proud to be a Super User! |
|
@Kosh
why the last 500 (24 Feb 2025) is 7%.. It should be 5% based on your range table and commision amount should be 25.
if it is not.then how do you calculate this 7% for 500?
Please clarify this.
Regards
sanalytics
First, create a calculated column in the Sales table to calculate the Year-To-Date (YTD) revenue for each sales person.
YTD Revenue =
CALCULATE(
SUM(Sales[Invoice Amount]),
FILTER(
Sales,
Sales[Sales Person] = EARLIER(Sales[Sales Person]) &&
Sales[Invoice Date] <= EARLIER(Sales[Invoice Date])
)
)
Next, create a calculated column to determine the commission rate based on the YTD revenue and the Target table.
Create a Calculated Column for Commission Amount: Finally, create a calculated column to calculate the commission amount for each row.
Commission Amount =
Sales[Invoice Amount] * Sales[Commission Rate]
Combine the Results: You can now combine these columns to get the total commission for each sales person.
Total Commission =
SUMX(
Sales,
Sales[Commission Amount]
)
By following these steps, you will be able to calculate the commission for each row in the Sales table based on the YTD
Proud to be a Super User! |
|
User | Count |
---|---|
16 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
23 | |
13 | |
13 | |
8 | |
8 |