Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Sales performance tracker

Hi everybody, 

I have the following challenge.

 

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

Submitted DateNameDesignationRegionStatus
10/10/2021ABCJr. Corp SalesWestApproved
10/10/2021CBASr. Corp SalesEastRejected
11/2/2021GFACorp SalesCentralApproved

 

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

 

NameDesignationTarget
ABCJr. Corp Sales3
CBASr. Corp Sales5
GFACorp Sales4

 

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.

 

MonthNameApprovedTargetScore or Variance
Jan 21ABC13-2
Feb 21ABC43+1

 

I would be happy about your support.

Need Help
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    4 years ago

    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]

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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