Prize/Comission calculation based on multiple criteria

Aucesar — Tue, 15 Aug 2023 19:12:06 GMT

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.

topic Prize/Comission calculation based on multiple criteria in DAX Commands and Tips

Prize/Comission calculation based on multiple criteria