cancel
Showing results 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

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.

 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
1 ACCEPTED SOLUTION
Super User

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 2
Resolver I

amazed by your skill. Thank you dataninja

Super User

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,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.