Hi Community,
Need to calculate commision/prize based on failure rate;
Data to example:
- TotalBrandValue (full month for example) - Fixed value I can use it fixed or from table/column, no problem.
| ProductionModelPrizeValue | Value |
| Apple | $400 |
| Nokia | $200 |
| Motorola | $100 |
-PrizeperDay (Measure already have it =PrizeValue / ProductionDaysMonth
| ProductionDaysMonth | PrizePerDay |
| 10 | $40 |
| | $20 |
| | $10 |
-FailureRange per brand
| FailureTax | Range |
| Apple | Between 0% and 1% Full Prize, between 1,1% and 2% Half Prize, over 2% Nothing |
| Nokia | Below 1% Full prize, over nothing |
| Motorola | Between 0% and 1% Full Prize, between 1,1% and 2% Half Prize, over 2% Nothing |
- MonthlyData
| Worker | ProductionModel | MonthlyAverageFailureTax |
| Lois | Apple | 0,90% |
| Lois | Nokia | 1,50% |
| Reggie | Nokia | 2,00% |
| Dannie | Motorola | 1,00% |
| Clara | Apple | 1,30% |
| Shawn | Nokia | 0,60% |
| Rueben | Nokia | 3,00% |
| Arron | Motorola | 4,00% |
| Erika | Nokia | 2,00% |
| Loren | Apple | 1,00% |
| Joel | Apple | 0,20% |
- Lois detailed Data
| Day | Worker | FailureRate | ModelWorked |
| 1 | Lois | 0 | Apple |
| 2 | Lois | 0,6 | Nokia |
| 3 | Lois | 0,1 | Motorola |
| 4 | Lois | 2,5 | Apple |
| 5 | Lois | 1,5 | Apple |
| 6 | Lois | 0,1 | Nokia |
| 7 | Lois | 0 | Motorola |
| 8 | Lois | | (Blank / Missed day) |
| 9 | Lois | 0,1 | Apple |
| 10 | Lois | 3,6 | Nokia |
Desire results
1st Step/Measure Check Monthly avegare is in the values for each brand, in this example Lois will receive prize for Apple, but not for Nokia.
2nd Step/Measure Check each day Lois worked for Apple brand and if it´s on prize range, in this example days 1, 4, 5 and 9 , but:
Day1 0% = Full prize (1.0)
Day4 2.5% = Nothing (0.0)
Day5 1.5% = HalfPrize (0.5)
Day9 0.1% = Full prize (1.0)
ApplePrize = 2.5 * PrizePerDayApple ($40) = $100 (Desire Result)
Thanks in advance for any help.
