How to Calculate Sales Bonus in PowerPivot?

[mrxlsx]

Hi all,

Good morning,

I have 3 tables namely fSa*****ok, dSalesman, dEmoluments.

1. fSa*****ok is l inked with dSalesman thru Salesm anKey column in the data model.

2. fSa*****ok is having a column namely SalesPoints for each Sales transaction.

3. dSalesman table is having SalesmanKey and their details like gender, age, ethnicity, education details.

4. dEmoluments is having the following columns.

dEmoluments:

YearlyTargetPoints	Bonus
2000	5000
4000	6000
6000	8000
8000	10000
10000	14000

<tbody>
</tbody>

Now the question is, I want calculate the the YearlyBonus in the fSa*****ok respective to their SalesTargetPoints.


What should be my formula in PowerPivot?

Thank you
mrxlsx

 

[scottsen]

It will be something like:

=CALCULATE(MAX(dEmoluments[Bonus]), FILTER(dEmoluments, dEmoluments[YearlyTargetPoints] >=[Sales Total]))

 

[mrxlsx]

Scottsen,

It is throwing up 14000 Bonus for all the records in the fSa*****ok. I think first we need to calculate the Yearly Sales Points achieved for each Salesman then compare the same with YearlyTargetPoints, right?

mrxlsx

 

[scottsen]

Ya, you would probably want to wrap the suggested measures in a SUMX across Years.