DAX Query two tables - Need help

[DiamonDave]

Hi everyone,

I have two queries on a legacy data set that I am scratching my head over.

Number 1:

I have two tables that are related by the FULLNAME field.  When an employee leaves, a record is created in the old_employee table, but their sales history is retained in the sales table.

DATA AND NAMES USED ARE ENTIRELT FICTIONAL AND FOR ILLUSTRATIVE PURPOSES ONLY - NO REAL NAMES USED IN THIS POST

Sales Table (example)

Company	Product Code	Units	Price	Total	Salesperson	Sales Contact
HCS Ltd	1212	50	2	100	Giles Farmer
ABC Ltd	1234	10	10	100	Joe Bloggs
CDE Ltd	1123	9	11	99	Joe Bloggs
DDX Ltd	1891	12	5	60	Giles Farmer
EPP Ltd	1451	17	3	51	Giles Farmer

,,,etc

Let;s say that Joe Bloggs leaves

FullName	Date	Contact
Joe Bloggs	11/MAY/2023	Dee Frost

I need to scan the sales table and if the FullName matches with an entry on the old_employee table, insert the new salesperson contact on every record in the sales contact field.  This should be left blank if there is no match, The result would look something like this...

Company	Product Code	Units	Price	Total	Salesperson	Sales Contact
HCS Ltd	1212	50	2	100	Giles Farmer
ABC Ltd	1234	10	10	100	Joe Bloggs	Dee Frost
CDE Ltd	1123	9	11	99	Joe Bloggs	Dee Frost
DDX Ltd	1891	12	5	60	Giles Farmer
EPP Ltd	1451	17	3	51	Giles Farmer

I was thinking of using SWITCH to do an if-then-else, but I'm new to Power Bi and so I don't have the syntax or experience to make this work.

Number 2: Relates to sales percentages.

Each salesperson has a total sales value, which is tabulated on a monthly report.  There is an expectation of making say £15'000 sales per month.  There is a conditional format on the total column that works out if they are on target for £180'000 sales, If a salesperson starts in July, they have missed APR, MAY, JUNE, so their figure for each of these months will be £0.00.  This skews the calculation, besides, the only way to know when they started selling is when they first record a sale.  All month's before this will show £0.00, which indicate no sales, and stuffs up the average calculation.

Can anyone think of some way to overcome this?

Thanks in advance.

[v-shex-msft]

Hi @DiamonDave,

#1, I'd like to suggest you create a calculate column on sales table to use the lookupvalue function to get corresponding value based on current table field values.

Contact =
LOOKUPVALUE ( Employee[Contact], Employee[FullName], Sales[FullName], BLANK () )

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

#2, I checked on your sample table records but not found any date fields existed in the sales table. Can you please share some more detail information or dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[DiamonDave]

Thank you for answering the question #1, I have not been able to implement your suggestion, but that may be because I lack the proper understanding. As I said, I am a bit of a newbie.

ALL DATA IS SAMPLE ONLY AND DOES NOT REFLECT ACTUAL MATERIAL IN DATA SET

Deleted Users Table (called deleted)

FullName	Date	Contact
Joe Bloggs	11/MAY/2023	Dee Frost

So if I read your answer correctly, I would be creating a calculated column, which works thus:

SalesContact =
LOOKUPVALUE ( Employee[Contact], Employee[FullName], Deleted[FullName], BLANK () )

Although I have a relationship between the two tables, Employee and Deleted, I don't get anything appearing in the column.  Perhaps I am doing something wrong.

In regards to #2, let's say for example that the data looks like the following:

SalesQuota (Now)

Salesperson	JAN	FEB	MAR	QTR	EXP	TOTAL	AVG	%
Bloggs, Jo	18000	12000	15000	4	45000	45000	15000	100.00
Farmer, Giles		17000	18000	4	45000	35000	17500	77.78
Frost, Dee	17250	19025	10750	4	47025	45000	15675	104.50

SalesQuota (Should Be)

Salesperson	JAN	FEB	MAR	QTR	EXP	TOTAL	AVG	%
Bloggs, Jo	18000	12000	15000	4	45000	45000	15000	100.00
Farmer, Giles		17000	18000	4	30000	35000	17500	116.66
Frost, Dee	17250	19025	10750	4	47025	45000	15675	104.50

So, as I said before each sales person has a total sales for each month, which is tabulated to represent like the (now) example above.  The expectation calculates £15'000 x number of months in quarter (3) to get a prediction on sales target,  However, if the member of staff missed a month, let's say Giles Farmer joined the company in February, he will not have any sales in Jan, but the table calculates as if he has recorded a zero.

Instead it needs to read the null value as just that, not a zero, so the calculation is 15000 x months with >0, which then adjusts the target accordingly to show as in the second version of the table.

I can't wait to overhaul this into something that works far better, but I am constrained for time and need to deal with this legacy in the short term.

If you could attach a sample database file to show how each of your suggestions (Q1 and Q2) works, that would be great.  I can retrofit the alterations onto my data sets by mapping field names.

Many thanks for looking at this and offering suggestions.

[v-shex-msft · Accepted solution]

HI @DiamonDave,

#1, It seems like in Sale tabel the 'name' field seems named 'Salesperson', perhaps you can use this to replace the searched table fields.

SalesContact =
LOOKUPVALUE ( Employee[Contact], Employee[FullName], Sales[Salesperson], BLANK () )

Comment:

Employee table means the table that you stored the employee information with contact.  Sales Table is the table that you want to add the contact info that lookup from employee table.

#2, OK, it seems like the target is a static value and sales table already include date values.

You can take a look at the following measure formula: (I try to add a variable table to summary total field values based on current salesperson and date year, month group. Then get the average from variable table result and divide with target to get the percentage value)

Employee AVG =
VAR target = 15000
VAR currEmployee =
    SELECTEDVALUE ( Sales[Salesperson] )
VAR startDate =
    CALCULATE (
        MAX ( Employee[Date] ),
        FILTER ( ALLSELECTED ( Employee ), [FullName] = currEmployee )
    )
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS (
            FILTER (
                ALLSELECTED ( Sales ),
                [Salesperson] = currEmployee
                    && [Date] >= startDate
            ),
            "Year", YEAR ( Sales[Date] ),
            "Month", MONTH ( Sales[Date] )
        ),
        [Salesperson],
        [Year],
        [Month],
        "monthlyTotal", SUM ( Sales[Total] )
    )
RETURN
    DIVIDE ( AVERAGEX ( summary, [monthlyTotal] ), target, BLANK () )

Regards,

Xiaoxin Sheng