Renewal Sales by Salesperson

[wenners68]

Hi,

I have 3 tables, 2020 sales, 2019 sales and a Salesperson table.

There's a relationship between 2020 sales and Salesperson.

I need to total what the salesperson sold in 2020 and then total what the clients he sold to in 2020 had bought in 2019 and show the difference. I can't link the salesperson to 2019 because of some of the salespeople in 2020 didn't work at the company in 2019. This would be easy to do in SQL, but with DAX I'm not sure.

[PaulDBrown]

@wenners68 

As Marcus suggests, I would go about this by merging the tables.

However, if you set up your model correctly, it is pretty simple to do:

And then by creating measures for the simple sum of sales for each year and the direct difference between the two:

Sales 2019 = SUM(Sales2019[Sales])

Sales 2020 = SUM(Sales2020[Sales])

Dif Sales = [Sales 2020]-[Sales 2019]

And setting up the matrix using the Salesperson and Client from their respective lookup/Dim tables, you get:

I believe this works because the leading filter is Saleperson from the Saleperson lookup table. This filters the Clients in the Sales 2020 table. Now, since the clients are unique in the Sales 2020 table, and there is a bi-directional one-to-one relationship with the Client lookup table, the filtering done by the Salespeson propagates through to the Client Lookup table which then in turn filters the Sales 2019 table. Does that make sense? (you could probably even leave out the Client lookup table altogether and establish a one-to-one relationship between both sales tables by linking the Client fields)

[Ashish_Mathur]

Hi,

Shouldn't there be a Salesperson column in Table 2 as well?

[JarroVGIT · Accepted solution]

Hi @wenners68 ,

This can be accomplished by the following calculated table:

Table = 
ADDCOLUMNS(VALUES('2020Sales'[Salesperson]), 
    "Total",
    VAR _curSP = [Salesperson]
    VAR _salesTableForcurSP = SUMMARIZE(FILTER('2020Sales', '2020Sales'[Salesperson] = _curSP), [Client], "curTotal", SUM('2020Sales'[Total]), "sales2019", 
        VAR _curClient = [Client]
        RETURN SUMX(FILTER(Sales2019, Sales2019[Client] = _curClient), [Total]))
    RETURN
    SUMX(_salesTableForcurSP, [curTotal]-[sales2019])
)

Result:

PBIX can be found here.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[mwegener]

Hi @wenners68 ,

why do you want to do it with DAX?

I would recommend a merge with Power Query.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.