One Dim table & 2 Fact tables (Sum of the result of divide from 2 tables/columns)

[PowerPaul]

Hi, I have 3 related tables, Employee info (Dimension), Total Hours (FACT TABLE containing the Total hours employee charges the company), and Project Actuals (FACT TABLE that includes the dollar amount and project hours for one specific project)

I have some measures which givs me the total hours and project hours. 

Total_Hours:=CALCULATE([Sum of Total Hours],'FY22-23_TotalHours'[Employee Id])

Project hours:= CALCULATE([Sum of Project Hours],'FY22-23_LaborActuals'[Employee ID])

I divide the project hours by the total hours to give me the % of total hours spent on the project called FTE Value. 

FTEV:= DIVIDE([Project_Hours],[Total_Hours])

Example I am 100% on the project so my project hours is the same as total hours so I equal 1.00 or 100% on the poject. 

Some people are 50% so they count as 0.50. 

I need to sum the result of the FTEV measure to get the total value by month in a pivot table. 

I can get the answer I want in a pivot table by then calculating the sum of the column FTEV for each employee. 

How can I get the same answer in DAX?

[johnt75]

You can get the total by

Total FTEV = SUMX(Employee, [FTEV])

[PowerPaul]

Hi Johnt75, I had to create a new data model using the same data but pulling from a folder instead of each individual file. This makes my monthly updates automatic as soon as I get new data and drop it in the folder. 

However, the formula above is not working as the original file. Instead of summing the results of the measure its counting the # of line entries that posted each month. 

Im utterly confused as I have both files open, with the exact same data & formula but different results. 

First data model: TotFTE_V:=SUMX('FY22-23_LaborActuals_FTE_CTR',[FTE_Value]) =1100.62

New data model: Tot_FTE_V:=SUMX('Labor Actuals',[FTEV]) = 2335

The only differences in the model is that in the first model all the data for contractors and Full time employees are in one table. There is no Hours for contractors so they are always zero. 

In the second model i have contractor data seperate so the formula is only working on Full Time employees.

The result should be the same but its not. 

[johnt75]

Try to identify rows which are in 1 table but not the other, then see what the [FTEV] measures return for those lines. If they have unique IDs then you could use DAX studio to get VALUES() from both tables and then do an EXCEPT

[PowerPaul]

Thank you John! 

That is what I was trying to do. So simple yet powerful. 

I still have a lot to learn but this helps greatly. 

Much appreciated. 

Paul

[amitchandak]

@PowerPaul , You need to have a common Employee table across these two tables and you can join both these tables to the employee table and plot the measure against the common employee table

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

[PowerPaul]

Hi Amitchandak, I have this set up already I believe. 

I have employee info table as the one side and both the total hours table and Project actuals table linked as the many sides. 

The issue is that when I put in a pivot, I can get the correct answer for each employee but not the total. I still have to manually sum each column in the pivot to get the total. 

The measure is calculating the total not as the sum of the column but rather as the total project hours divided by the total hours and has one answer for example 0.76. 

I want to sum each employees FTE Value.

So after the measure is calculated FTEV:= DIVIDE([Project_Hours],[Total_Hours]) I need to then sum each employees individual answer to come up with the total FTEV. 

For example manually in Excel it is, sum(b2:b136) = 126.30 not the 0.76 above.