Show Sales amount employee-wise according to allocated costs

[Philipp_ISARTAX]

Hello,

I try to find a solution for the following setup. I tried several DAX-Formulas, Modelling and so on. But I didn't find a solution.

Basis is one Table with all Sales:

Invoice Date	Invoice No.	Order ID	Product No.	Sales amount
05.12.2022	202201	5224	33	500,00
05.12.2022	202201	6478	33	450,00
05.12.2022	202202	5224	24	250,00
09.12.2022	202203	5224	35	800,00
13.11.2023	202306	8236	33	350,00
13.11.2023	202306	8236	33	400,00
13.11.2023	202307	6478	24	300,00
15.12.2023	202311	8236	35	325,00
15.12.2023	202311	6478	24	250,00
15.12.2023	202311	8236	35	125,00

A second table shows all Costs occured on each Order (Employeewise):

Order ID	Employee No.	Employee Name	Working Date	Cost
5224	1	Max	02.06.2022	55,00
6478	2	Thomas	08.04.2022	80,00
8236	4	Susan	08.08.2023	20,00
5224	3	Lisa	01.12.2023	45,00
6478	5	Marcus	02.08.2022	90,00
6478	2	Thomas	15.07.2022	78,00
5224	4	Susan	07.11.2022	65,00
6478	3	Lisa	09.04.2023	53,00
8236	1	Max	08.12.2023	74,00
5224	2	Thomas	21.12.2022	98,00
6478	4	Susan	02.06.2022	55,00
6478	3	Lisa	08.04.2022	80,00
5224	5	Marcus	08.08.2023	20,00
6478	2	Thomas	01.12.2023	45,00
8236	4	Susan	02.08.2022	90,00
5224	3	Lisa	15.07.2022	78,00
6478	1	Max	07.11.2022	65,00
6478	2	Thomas	09.04.2023	53,00
5224	4	Susan	08.12.2023	74,00
6478	3	Lisa	21.12.2022	98,00
8236	5	Marcus	02.06.2022	45,00
5224	2	Thomas	08.04.2022	90,00
6478	4	Susan	08.08.2023	78,00
6478	3	Lisa	01.12.2023	65,00

Additionally I created two Dimension Tables (Employees and Orders) which were linked as follows:

I would like to get a report which gives me the Sales amount per employee (the sales amount should be split up according to the costs occured on the project). 

So far I'm able to add a column in the Cost Table which calculates the relevant sales amount.

But my big challenge is, that I also would like to sort the divided employeewise sales amount by invoice date. 

Target is a visual which shows the sales amount on the X-Achsis and the Invoice-Date on the Y-Achsis. And this amount should be able to be filtered by employee.

I'm really getting crazy because I tried several solutions. But nothing worked sufficient. I hope somebody can help me.

Kind regards,

Philipp

[Anonymous]

Hi @Philipp_ISARTAX ,

What's your expected result? Could you please provide some examples and explain the expected result base on the provided sample data? For example: OrderID 5224,  the expected values is 155000?

Best Regards

[Philipp_ISARTAX]

Hi @Anonymous ,

Thanks for your reply. Your solution was not the expected value. 

I wanted to split up the sales volume of an Order (e.g. Order 5224; 1,550.00 € total sales) to the employees who worked on the order. The split up should be based on the allocated costs (total costs for Order 5224 = 525.00 €. 

In a first step this seemed easy to realize as additional columns in the table "All costs". Following sample filtered by order 5224:

But if I now want to visualize the sales per employee in a visual I'm not able to show the allocated sales in relation to the invoice date (e.g. line graph showing development of sales over the year).

Therefore I thought about a solution starting with table "Sales".

At the end I would have loved to use a measure for this without adding additinal columns.

However in the meantime I found a solution. It is not my prefered solution but it works:

I dublicated the table sales. The new table was named sales_splitup. I added the table Dim-Employees as column to table "Sales_splitup". Than I expanded the additional table within the added column. 

This resulted in a "Sales_splitup"-table with 5 times more lines. Each Sales Line was 5-times multiplied for each employee.

This made it possible to calculate the sales-split employee-wise in the sales_splitup table.

Find the screenshots of my doing as follows:

And at last the result:

This made me able to create following visual:

At the end this helped me for my solution, but it blew up the volume of the sales table. In the real data I now have two sales tables. The one which is employee-wise has 1.2 Mio. lines (caused by the multiple with 20 employees).

If you have an Idea how this can be solved with a measure I would be thankful.

Best regards