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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
santoshlearner2
Resolver I
Resolver I

Calculate Reduction in Fee based on Criteria

Dear Stalwarts,

I could not solve this problem, struggling for week, finally gave up, ( I am not expert just a learner, learning from mistakes).

Problem is. I have a list of products along with the distributor selling those products and the respective sales amount, For each sales the distributor is paid a comission based on the commission rate, Is there a way if the Ratio of the commision rat eto the cost rate goes above say 80%, then the commission should be reduced and accordngly the reduction in rate and then the revised fee.

 

Also can we make it dynamic by not keeping 80% but the user can change as per his input.

 

A big thanks to all the people who can assist, i gave up on this one.  A Big thank you to everyone.

 

 

 

 

ProductDistributor codeComission Rate %SalesFEE (Sales X Comission Rate) (Z ) Cost %Ratio of Cost % to Comission % (A)Benchmark (B )Reduction C =  (A- B) Only Positive %RemarksAction Deduction in Fee Amount ( Column C = (A - B) Revised Fee Amount= (Z - C)
CycleDistributor 10.21%1500031.500.50%42.00%80.00%-38.00%Ratio less than 80%No Reduction 31.50
BikesDistributor 20.50%25000125.001.00%50.00%80.00%-30.00%Ratio less than 80%No Reduction 125.00
CarsDistributor 30.56%85000476.000.65%86.15%80.00%6.15%Ratio more than 80%Reduce to 80%29.29230769446.71
Spare PartsDistributor 40.60%4502.700.72%83.33%80.00%3.33%Ratio more than 80%Reduce to 80%0.092.61
PensDistributor 50.25%1500037.500.30%83.33%80.00%3.33%Ratio more than 80%Reduce to 80%1.2536.25
BooksDistributor 60.35%2500087.500.56%62.50%80.00%-17.50%Ratio less than 80%No Reduction 87.50
PaperDistributor 70.43%85000365.500.72%59.72%80.00%-20.28%Ratio less than 80%No Reduction 365.50
RubberDistributor 80.54%4502.430.72%75.00%80.00%-5.00%Ratio less than 80%No Reduction 2.43
CupsDistributor 90.66%4502.970.72%91.67%80.00%11.67%Ratio more than 80%Reduce to 80%0.34652.62
1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

Hi @santoshlearner2 ,

 

There are many ways to produce your required output and one of them is as follows:

You can input the benchmark% in the yellow highlighted part and let the max value of that as the benchmark % parameter.  

Reduction Only Positive % = if([Ratio of Cost% to Comission % (A)]>[Benchmark%],[Benchmark%],[Ratio of Cost% to Comission % (A)])
Revised Fee Amount = sumx(values('Table'[Product]),[Sales]*[Cost%]*[Reduction Only Positive %])

DataNinja777_0-1718204306076.png

I omit the detailed explanations, but attach the pbix file for your reference.  

Best regards,

View solution in original post

2 REPLIES 2
santoshlearner2
Resolver I
Resolver I

amazed by your skill. Thank you dataninja

DataNinja777
Super User
Super User

Hi @santoshlearner2 ,

 

There are many ways to produce your required output and one of them is as follows:

You can input the benchmark% in the yellow highlighted part and let the max value of that as the benchmark % parameter.  

Reduction Only Positive % = if([Ratio of Cost% to Comission % (A)]>[Benchmark%],[Benchmark%],[Ratio of Cost% to Comission % (A)])
Revised Fee Amount = sumx(values('Table'[Product]),[Sales]*[Cost%]*[Reduction Only Positive %])

DataNinja777_0-1718204306076.png

I omit the detailed explanations, but attach the pbix file for your reference.  

Best regards,

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!