Calculate Reduction in Fee based on Criteria

[santoshlearner2]

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

[santoshlearner2]

amazed by your skill. Thank you dataninja

[DataNinja777 · Accepted solution]

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,