Calculating different incentives when the number of units changed

[Anonymous]

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.

[v-danhe-msft]

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 

[Anonymous]

Hi Daniel,

Thanks for the reply!

I have attached the pbix file with dummy data.

https://1drv.ms/u/s!Att5ET2256YlhRt14L7UBYzrqtCI

Thanks.

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

You could refer to below formula:

Measure = SUMX('Salesperson','2019'[Incentive])

Result:

Regards,

Daniel He

[Anonymous]

Thanks @v-danhe-msft !

Got the results that i wanted.