The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
I have encountered a problem when calculating incentives.
This is the incentive table for each salesperson when they have sold the number of units required.
This is an example of how it would be like if I split it among the salespeople.
The total units are correct.
However, the total $ amount is calculated as 115 units X $500 instead of the individual amount.
The total incentive should have been $54,900 instead of $57,500.
Any idea how I am able to solve this such that the amount would show $54,900 instead of $500 X 115 Units?
Some background information:
Units are derived from the count of filtered values of a row:
Units = CALCULATE(COUNTA('2019'[Buyer]), '2019'[Buyer] IN { "PR" })
Then the incentive is as follow:
Incentive = IF([Units] >= 7, (500*[Units]), IF([Units] = 6, (450*[Units]), IF([Units] = 5, (400*[Units]), IF([Units] = 4, (350*[Units]), IF([Units] = 3, (300*[Units]), IF([Units] = 2, (250*[Units]), IF([Units] = 1, (200*[Units]), 0) ) ) ) ) ) )
Many thanks in advance.
Solved! Go to Solution.
Hi @Anonymous ,
You could refer to below formula:
Measure = SUMX('Salesperson','2019'[Incentive])
Result:
Regards,
Daniel He
Hi @Anonymous ,
I could not reproduce your [units] coulumn:
Units = CALCULATE(COUNTA('2019'[Buyer]), '2019'[Buyer] IN { "PR" })
Could you please share your pbix file to have a test if possible?
Regards,
Daniel He
Hi Daniel,
Thanks for the reply!
I have attached the pbix file with dummy data.
https://1drv.ms/u/s!Att5ET2256YlhRt14L7UBYzrqtCI
Thanks.
Hi @Anonymous ,
You could refer to below formula:
Measure = SUMX('Salesperson','2019'[Incentive])
Result:
Regards,
Daniel He