Forum Discussion
Resolver IICalculate 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.
| Product | Distributor code | Comission Rate % | Sales | FEE (Sales X Comission Rate) (Z ) | Cost % | Ratio of Cost % to Comission % (A) | Benchmark (B ) | Reduction C = (A- B) Only Positive % | Remarks | Action | Deduction in Fee Amount ( Column C = (A - B) | Revised Fee Amount= (Z - C) |
| Cycle | Distributor 1 | 0.21% | 15000 | 31.50 | 0.50% | 42.00% | 80.00% | -38.00% | Ratio less than 80% | No Reduction | 31.50 | |
| Bikes | Distributor 2 | 0.50% | 25000 | 125.00 | 1.00% | 50.00% | 80.00% | -30.00% | Ratio less than 80% | No Reduction | 125.00 | |
| Cars | Distributor 3 | 0.56% | 85000 | 476.00 | 0.65% | 86.15% | 80.00% | 6.15% | Ratio more than 80% | Reduce to 80% | 29.29230769 | 446.71 |
| Spare Parts | Distributor 4 | 0.60% | 450 | 2.70 | 0.72% | 83.33% | 80.00% | 3.33% | Ratio more than 80% | Reduce to 80% | 0.09 | 2.61 |
| Pens | Distributor 5 | 0.25% | 15000 | 37.50 | 0.30% | 83.33% | 80.00% | 3.33% | Ratio more than 80% | Reduce to 80% | 1.25 | 36.25 |
| Books | Distributor 6 | 0.35% | 25000 | 87.50 | 0.56% | 62.50% | 80.00% | -17.50% | Ratio less than 80% | No Reduction | 87.50 | |
| Paper | Distributor 7 | 0.43% | 85000 | 365.50 | 0.72% | 59.72% | 80.00% | -20.28% | Ratio less than 80% | No Reduction | 365.50 | |
| Rubber | Distributor 8 | 0.54% | 450 | 2.43 | 0.72% | 75.00% | 80.00% | -5.00% | Ratio less than 80% | No Reduction | 2.43 | |
| Cups | Distributor 9 | 0.66% | 450 | 2.97 | 0.72% | 91.67% | 80.00% | 11.67% | Ratio more than 80% | Reduce to 80% | 0.3465 | 2.62 |
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 %])I omit the detailed explanations, but attach the pbix file for your reference.
Best regards,
2 Replies
santoshlearner2amazed by your skill. Thank you dataninja
- DataNinja777
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 %])I omit the detailed explanations, but attach the pbix file for your reference.
Best regards,