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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]YearlyTargetPoints[/TD]
[TD]Bonus[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]4000[/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD]6000[/TD]
[TD]8000[/TD]
[/TR]
[TR]
[TD]8000[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]10000[/TD]
[TD]14000[/TD]
[/TR]
</tbody>[/TABLE]


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.