Calculate Average Sales based on preasence at company

[Anonymous]

Hello everyone,

I am experiencing some difficulties in finding a way to automate a rather simple calculation.

For the purpose of clarity let us assume I have 3 tables. One with the records per salesperson (and ID) and his/hers respective dealership (left table below), a second one with the ID of the salesperson and the month/year this person as joined or left the company (right table below) and a Date table that serves as support for the dashboard .

Tables

In the report I will need to create a matrix that shows the average sales per salesperson per dealership, for any given time period. This would be relatively easy, I would simply divide the total number of sales per dealership by a CountDistinct salesperson on the first table. The problem is that if a Salesperson does not have a sale in a specific period but is working in the company his “presence” will not be counted in the CountDistinct given that he simply will not show up in the filtered first table.  

I will have to cross this information with the second table to check the number of active salesperson on that period (even if they did not make a sale).

One solution I imagined would involve creating a column in the second table where each salesperson line would unfold into several lines (one for each month and year) with a third column saying yes or no (1 or 0) for him being or not in the company at the time. An immediate problem I see with this is that the Date table would have to filter this table and also filter the first table which would in turn be filtered by this table. I believe this would not be possible due to conflicts in the relationships.  

 As anyone faced a similar problem before? If I wasn’t clear please let me know, any help would be much apreciated.

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the following steps to meet your requirement.

1. Create a date table, and add a slicer based on it.

date table = CALENDAR("2019/1/1","2020/12/31")

2. Then we can create a measure,

Measure = 
var selected_max = MAX('date table'[Date])
var selected_min = MIN('date table'[Date])
var mi = MAX(selected_min,MIN('Table (2)'[Enter]))
VAR ma = MIN(selected_max,MAX('Table (2)'[Leave]))
var m = DATEDIFF(mi,ma,MONTH)
return
IF(m<0,BLANK(),DIVIDE(SUM('Table'[Sales]),m))

The result like this, we can use the slicer to control the value.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.       

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello again @v-zhenbw-msft ,

A problem as occurred, there is the possibility that a salesperson enters and leaves the company multiple times (e.g. maternity leave).

Let me simplify the problem, let us only calculate the number of months a person was working during a time period and forget the sales qty part.

Basically I would need a measure that would calculate the Date Difference between the Max date between Slicer and “Enter” Column and Min date between Slicer and “Leave” Column for each row (so I could sum them up for each person).

Below is an example, Filter X and Filter Z are two possible selected dates in a between Date Slicer.

Using your code, I would need the Max and Min functions for the “mi” and “ma” Vars to be transformed into Maxx and Minx functions respectively, however the functions do not allow me to do that, they only calculate for a single expression.

Another solution I tested was to create a column in the Times table which would calculate the difference for each row, however the created column does not update with the slicer dynamically.

Do you have any suggestion regarding this issue?

This is a problematic topic to explain if I wasn’t clear please let me now and thank you in advance.

[Anonymous]

I think I figured it out, basically create a nem measure wheer I use SUMX(Time_Table,Measure1)

[Anonymous]

Hi @v-zhenbw-msft ,

First of all thank you for your help.

I will need to make some ajustments to the relationship model in order to avoid some conflicts but it will work pefectly, thank you.