allocation of expenses by sales in dax from two unrelated columns

alexstarikov0 1 Reputation point
2020-10-02T09:08:26.813+00:00

I have 2 tables:

  1. sales contains: 1) employee 2) customer 3) sales amount

2) costs contains 1) customer 2) operation 3) expenses amount
descrition: salary data in costs table displayed as customer, which is equal to employee from sales table. And employees have only salary expenses.

customers from sales and costs are related and employee is not related to customer..

And I'm required to distribute all salary expenses from costs by sales customers.
So I need to have amount related to employee who soled goods to clients to display distributed expense by each sale line (in case if it would be displayed by goods the amount would also be distributed by goods).

what i require displayed in desired view column:
29809-image.png

I have following formulas for 07,DirectSalesV:

07,DirectSalesV:=
CALCULATE('costs'[expenseAmount];
FILTER('client';'client'[client]=
CALCULATE(
VALUES('employee'[employee]);
FILTER('employee';'employee'[employee]='client'[client]))))

07,DirectSalesV:=
CALCULATE(
SUMx('costs';[expenseAmount]);
INTERSECT(
VALUES('costs'[client]);
VALUES('employee'[employee]));;
)

Please help to find out the solution

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,282 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,821 Reputation points
    2020-10-05T02:30:30.727+00:00

    Hi,

    Sorry to say that the description is hard to comprehend , also the requirement is hard to be understood clearly.

    Could you post sample data of the original two table, and desired outcome. So we could understand the request better?

    Regards,
    Lukas


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.