Forum Discussion
Employee FTE calculation
Hi,
I have data similar to tables below. Fact table that I se connects to number of other dimension tables and so number of rows per each assignment id are much larger but I have simplified it to make it easy to understand my issue.
Employee table is joined to Fact table on Employee ID and Assignment Table is joined to fact table on composite key (Assignment ID + Start Date + End Date)
I am trying to get to an output as below and I am not able to calculate Employee FTE values accurately. Appreciate if you can offer any help as I am new to Power BI and struggling as I have spend few hours on this but could not get it right.
| Power BI report output | |||
| Employee Name | Assignment ID | Assignment FTE | Employee FTE |
| A | 11 | 1 | 1 |
| B | 21 | 0.5 | 0.7 |
| B | 22 | 0.2 | 0.7 |
| C | 31 | 0.3 | 0.7 |
| C | 32 | 0.1 | 0.7 |
| C | 33 | 0.3 | 0.7 |
Thank you,
-Raj
gottapu how about changing the code like this?
Employee FTE:=IF(COUNTROWS('Fact'),SUMX(ALL(Assignment[Asgn ID]),CALCULATE(MAX('Fact'[Assignment FTE]),ALL(RepGroup[Rep Group Name]),ALL('Fact'[Assignment FTE]))))
12 Replies
gottapu , Try a measure like
Employee FTE =calculate(sum(Table[Assignment FTE]), filter(allselected(Table),[Employee Name] = max([Employee Name])))
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.Thanks Amit for your quick reply. Unfortunately, after trying above, I am getting Employee FTE as sum of all Assignment FTE values in the fact table and hence they are way more than what I am trying to get. For Employee ID =1, I am getting Employee FTE as value 3.
Thanks
-Raj
