Forum Discussion

santoshlearner2's avatar
santoshlearner2
Icon for Resolver II rankResolver II
2 years ago
Solved

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...
Need Help
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    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,

DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper User
2 years ago

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,