Forum Discussion

unknown_anony's avatar
unknown_anony
Helper II
2 years ago
Solved

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 c...
Need Help
  • Anonymous's avatar
    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.

     

     

DataNinja777's avatar
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,