Calculate difference from two facts table

[unknown_anony]

I have a dimension table with Emp details, I have a factstable with all the revnue details  generated by each employee by booking hours and another facts table which has all the expenses. I want to calculate EBIDTA = Total revenue - total Expenses. One table is connected with emp ID column with one to many relation with Emp details which is absolutly fine and another is many to many connected  with company code column in both the tables.
I want to show EBIDTA calculated among each manager

Thank you
All Advises are welcomed

[DataNinja777]

Hi @unknown_anony 

I understand that revenues fact tables have many to one relatinoship with the employee fact tables.  On the other hand, there are costs of payroll of non-revenue generating department employees to be allocated to revevenue generating product/ business units.  Typically, companies tends to use allocation drivers such as % of overall sales to allocate costs of non-revenue genrating department employees to product and service revenues to get product level EBITDA and dax is excellent for performaing such allocation tasks. 

Best regards,

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.

[unknown_anony]

Here is sample data

Emp Details -  Dimension table. Personal number is Unique column

Revenue Table- Facts table

Expense Table - Fact table 2 connected with company code 

Hope this helps

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solutions @Ashish_Mathur  and @DataNinja777  provided, and i want to offer some more information for user to refer to.

hello @DataNinja777 , based on your description, you can refer to the following solution.

1. Create a new table named company.

Company = DISTINCT(UNION(VALUES(Expense[Company Code]),VALUES(Revenue[Company Code])))

2.Create 1:n relationships among company, expense,revenue table. the key column is company code.

3.Create the following measures

Revenue = SUM(Revenue[Revenue])

Cost = CALCULATE(SUM(Expense[Expenses]),CROSSFILTER(Company[Company Code],Revenue[Company Code],Both))

EBIDTA = [Revenue]-[Cost]

Output

Best Regards!

Yolo Zhu

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

[Ashish_Mathur]

That isn't what i requested you for in my previous message.