Commission Based on number of Jobs

[Anonymous]

Hi there,

i am trying to figure out a measure i can use to calculate commission for Area Managers if the job reaches a threshold at end of month.

For e.g if Area manager has done more than 400 jobs, they get $2 per job for anything over 400 jobs & if they have done between 600-800, they get $3 per job.

I have the number of jobs table.

I have the rates table

I have Area Managers table

Please advise?

[v-henryk-mstf]

Hi @Anonymous ,

As @Ashish_Mathur  said, what is the basis for the values in the expected results provided? I can’t seem to understand, please provide the correct number of tests and I will answer you as soon as possible.

Looking forward to your reply.

Best Regards,
Henry

[Anonymous]

@amitchandak  - Any help here?

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format?

You can use a measure like, Assuming you have a Jobs measure

Sumx( Values(Table[Area Manger]) ,
Switch(True(),
[Jobs] > 600 , 3 *[Jobs],
[Jobs] > 400 , 3* [Jobs],
0))

[Anonymous]

@amitchandak ,

here are the tables,

Basically, i am trying to see how to get an output as per my last table below where Area Managers would know when they are eligible for commission once they reach their threshold as per the rates table.

Please note Area Manager B & E has multiple threshold criteria.

Rates Table
From	To	Rate	From Date	To Date	Area Managers
400		$2.	1/06/2021	30/06/2021	A
400		$2.	1/06/2021	30/06/2021	B
0	400	$0.5	1/06/2021	30/06/2021	B
900		$2.	1/06/2021	30/06/2021	D
400	600	$2.	1/06/2021	30/06/2021	E
601	800	$2.	1/06/2021	30/06/2021	E
801		$3.	1/06/2021	30/06/2021	E
900		$3.	1/06/2021	30/06/2021	F
People Table
Syndicate Name	Area Managers
Platform 1	A
Platform 2	B
Platform 3	C
Platform 4	D
Platform 5	E
Output required
Area Managers	Syndicate Name	Monthend	Sum of jobs	Commission
A	Platform 1	30/06/2021	400
B	Platform 2	30/06/2021	900
C	Platform 3	30/06/2021	600
D	Platform 4	30/06/2021	800
E	Platform 5	30/06/2021	500

[Ashish_Mathur]

Hi,

How did you arrive at the numbers in the last column of the desired table.  None of the previous 2 tables show the number of jobs completed.

[Anonymous]

@Ashish_Mathur 

Sum of jobs you see here is the criteria table.

e.g If Area Manager A achieves >400 jobs a month , commission rate $2 should be calculated based on the number of jobs greater than 400. 

e.g 2 if If Area Manager B achieves <400 jobs a month , commission rate $0.5 should be calculated based on the number of jobs less than 400.

Output required
Area Managers	Syndicate Name	Monthend	Sum of jobs	Commission
A	Platform 1	30/06/2021	400
B	Platform 2	30/06/2021	900
C	Platform 3	30/06/2021	600
D	Platform 4	30/06/2021	800
E	Platform 5	30/06/2021	500

[Ashish_Mathur]

Hi,

In the last table, could you also fill the amount in the Commission table in an MS Excel file with your formula so that i can follow your logic clearly and also tally my answer with yours.

[Anonymous]

I have slighlty adjusted the above table to below : Instead of Sum of jobs, i have changed it to count of jobs.

So my purpose is to calculate commission when it goes beyond the threshold value of jobs?

here is the formula which i used & not able to arrrive at any conclusion.

	Output required
	Area Managers	Syndicate Name	Monthend	Count of jobs	Commission calculation required	Rates table Threshold
	A	Platform 1	30/06/2021	500	2*100	>400
	B	Platform 2	30/06/2021	900	2*500	>400
	B	Platform 2	30/06/2021	200	0.5*200	<400
	D	Platform 4	30/06/2021	1000	2*100	>900
	F	Platform 5	30/06/2021	1000	3*100	>900

If(Area Manager="A" & table(count of Jobs) >400, $2 *count of jobs > 400 )

example if Area manager has done 500 jobs at monthend, then commission should be calculated based on difference of jobs extra over 400(500-400) jobs i.e $2 * 100 jobs

if(And(Area Manager="B"& Table(count of jobs)>400,$2 * count of jobs > 400 ,  Table(count of jobs)<400,$2 * count of jobs < 400 jobs).

[Ashish_Mathur]

Hi,

Is countofjobs a measure?  Share the link from where i can download your PBI file.

[Anonymous]

@Ashish_Mathur 

yes it's a measure. It counts the number of jobs for the area for the area manager .

[Ashish_Mathur]

As requeted, share the download link of the PBI file with your measure already written.

[Anonymous]

@Ashish_Mathur 

I am unable to share the file due to confidential reasons: 

Here is te measure i used for Count of Jobs:

no of jobs= COUNT(Jobstable[JobId])

[Ashish_Mathur]

Hi,

Please anonymise data in your PBI file and share the download link.  Now with another table (Jobs Table) being introducted by you, i do not want to make any guesses about the structure of your data model, hence the request to share an anonymised PBI file.