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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
santoshlearner2
Helper II
Helper II

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
Helper II
Helper II

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors