Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
MAKroeze
Regular Visitor

Calculating taxes or commission in Power Bi using a progressive scale

I need to calculate commission using a progressive scale. Below is a sample of the commission scale table.

So for a sale of $35000 the commission would be calculated by: $570 (starting commission) + $10000 * 0,0220 = $790

Seems simple, but can't figure out how to calculate this using a DAX formula. Any suggestions?

 

Category Lower  Upper Starting commissionPercentage
0 - 25.000                       0            25.000,000,002,28
25.000,01 - 100.000       25.000,01          100.000,00570,002,20
100.000,01 - 200.000     100.000,01          200.000,002220,002,32

 

1 ACCEPTED SOLUTION
vicky_
Super User
Super User

Try this: 
Also, I don't know if you meant to have $10000 or $35000 in the formula, but just change the numbers if I misunderstood the problem.

 

commission = 
var hardCodedSale = 35000
var startingCommission = CALCULATE(MAX('Table'[Starting commission]), hardCodedSale >= 'Table'[ Lower ] && hardCodedSale <= 'Table'[ Upper ])
var percentage = CALCULATE(MAX('Table'[Percentage]), hardCodedSale >= 'Table'[ Lower ] && hardCodedSale <= 'Table'[ Upper ])

return startingCommission + hardCodedSale * percentage

 

vicky__0-1687216846249.png

 

View solution in original post

2 REPLIES 2
MAKroeze
Regular Visitor

Thanks for the quick response! 

I should have asked the question more clearly. 

For a sale of $35000 the commission would be calculated by:

$25000 * 0,0228 = $570 

$10000 * 0,0220 = $220

Total commission = $790

 

So the percentage commission only applies to the amount that is considered in that category ($25000 in category 0-25000, $10000 in category 25000,01 - 100000).

 

vicky_
Super User
Super User

Try this: 
Also, I don't know if you meant to have $10000 or $35000 in the formula, but just change the numbers if I misunderstood the problem.

 

commission = 
var hardCodedSale = 35000
var startingCommission = CALCULATE(MAX('Table'[Starting commission]), hardCodedSale >= 'Table'[ Lower ] && hardCodedSale <= 'Table'[ Upper ])
var percentage = CALCULATE(MAX('Table'[Percentage]), hardCodedSale >= 'Table'[ Lower ] && hardCodedSale <= 'Table'[ Upper ])

return startingCommission + hardCodedSale * percentage

 

vicky__0-1687216846249.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.