Sales performance tracker

[Anonymous]

Hi everybody, 

I have the following challenge.

The following table is available to me for our sales submit.

Submitted Date	Name	Designation	Region	Status
10/10/2021	ABC	Jr. Corp Sales	West	Approved
10/10/2021	CBA	Sr. Corp Sales	East	Rejected
11/2/2021	GFA	Corp Sales	Central	Approved

And I want every employee to have a permanent monthly target based on the designation.

Name	Designation	Target
ABC	Jr. Corp Sales	3
CBA	Sr. Corp Sales	5
GFA	Corp Sales	4

So I can see the performance of each employee based on the number of approval statuses each month with the remaining or exceeded targets, like this.

Month	Name	Approved	Target	Score or Variance
Jan 21	ABC	1	3	-2
Feb 21	ABC	4	3	+1

I would be happy about your support.

[Anonymous]

Hi @Anonymous ,

Create a relationship between two tables by using column [name].

Then create a measure as below:

Measure = 
var c_approved = CALCULATE(COUNT(sales[Status]),FILTER(sales,sales[Status]="Approved"))
return
c_approved-SELECTEDVALUE(Target[Target])

Best Regards,

Jay

[amitchandak · Accepted solution]

@Anonymous , Join the second Table with first Table  and also join first table with date table and have month year column

Target Score=

Sumx(Table1, related(Table2[Target])

Variance = Countrows(Filter(Table1[Status] = "Approved")) - [Target Score]

[Anonymous]

Hi, thank you for replying me. But I want to show you, why the variance get the wrong calculation? Can you provide me again? I using Merge Queries as new, from first table to second table with LeftOuter join.