Calculate difference from two facts table

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

Need Help

Anonymous
2 years ago
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.
Calculate difference from two facts table.pbix73 KB

5 Replies

Ashish_Mathur
Super User
2 years ago
Hi,

Share data in a format that can be pasted in an MS Excel file. Show the expected result very clearly.
- unknown_anony
  Helper II
  2 years ago
  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
  - Ashish_Mathur
    Super User
    2 years ago
    That isn't what i requested you for in my previous message.
DataNinja777
Super User
2 years ago
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,