topic Re: Employee FTE calculation in DAX Commands and Tips

Employee FTE calculation

gottapu — Mon, 19 Oct 2020 05:15:14 GMT

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

Re: Employee FTE calculation

amitchandak — Mon, 19 Oct 2020 05:44:03 GMT

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.

Re: Employee FTE calculation

gottapu — Mon, 19 Oct 2020 06:21:24 GMT

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

Re: Employee FTE calculation

wdx223_Daniel — Mon, 19 Oct 2020 06:53:09 GMT

gottapu i think this code can help you

Re: Employee FTE calculation

gottapu — Mon, 19 Oct 2020 07:23:43 GMT

Thanks much wdx223_Daniel. Really appreciate you taking the time to replicate our scenario. Below solution has got me very close. We are displaying Assignment ID from dimension and when we do that Employee FTE and Assignment FTE are now showing same values (Employee FTE values not rolling up). But when we show Assignment ID from fact table, Employee FTE values are rolling up correctly. In reality, we have Assignment Number in our Assignments dimension that we show in our report. Appreciate, if you can let me know how best to adjust code when we are using Assignment ID from Assignment dimension in our report.

Thank you

-Raj

Re: Employee FTE calculation

wdx223_Daniel — Mon, 19 Oct 2020 07:25:33 GMT

gottapu how about adjust the code like this

Re: Employee FTE calculation

gottapu — Mon, 19 Oct 2020 07:34:39 GMT

Thank you so much wdx223_Daniel . I have been working on this calc for last few days and breaking my head. I am relatively new to Power BI DAX code. I have gone through Plural sight DAX videos and other DAX documentation available on web but still struggled to get to correct number. You have made my day. Thank you so much for going extra mile to help.

Thanks,

-Raj

Re: Employee FTE calculation

gottapu — Mon, 26 Oct 2020 05:23:49 GMT

Hi wdx223_Daniel,

I have run into a slight problem where if I bring in additional column from another dimension, FTE values appears to be not rolling up. For example, I have Representation Group dimension that is connected to my fact on Rep group id field. For Example, Assignment IDs 21 has a representation group of 'Faculty' value and and Assignment ID 22 as a representation group of 'Clinician'. Though representation groups are at the assignment level, they are connected to Fact table using a different key (i.e Rep ID). Is there a way I can include Representation group in my report but still calculate FTE by Employee?

Output that I am looking for:

Employee Name	Assignment ID	Rep Group	Assignment FTE	Employee FTE
A	11	Faculty	1	1
B	21	Faculty	0.5	0.7
B	22	Clinician	0.2	0.7
C	31	Faculty	0.3	0.7
C	32	Clinician	0.1	0.7
C	33	MG Fcaulty	0.3	0.7

Rep Group Table:

Rep Group ID	Rep Group Name
1	Faculty
2	MG Faculty
3	Clinician

For Employee B and C, I am getting same values for Employee FTE and Assignment FTE. Apreciate any further help.

Thanks,

-Raj

Re: Employee FTE calculation

wdx223_Daniel — Mon, 26 Oct 2020 05:38:49 GMT

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]))))

Re: Employee FTE calculation

gottapu — Mon, 26 Oct 2020 05:49:28 GMT

Thanks much wdx223_Daniel. That does the job for me.

I just have a follow up question. If I create a measure like this it looks like it only works for coded secnario and if my users drag and drop additional fields from additional dimensions, calculation poentially displays Assignment FTE values under Employee FTE field. Is there a way we can calculate it regardless of what additional columns (from dimensions that I have not specificed in my calc) users add to thier reports?

Thanks,

-Raj

Re: Employee FTE calculation

wdx223_Daniel — Mon, 26 Oct 2020 06:30:45 GMT

gottapu please try this code, i don't think it is the best solution, this measure ignores the assignment id.

Employee FTE2 := IF ( COUNTROWS ( 'Fact' ), SUMX ( VALUES ( Employee[Name] ), VAR _eply = Employee[Name] RETURN SUMX ( CALCULATETABLE ( SUMMARIZE ( 'Fact', 'Fact'[Assignment FTE], Assignment[Asgn ID] ), ALL ( 'Fact' ), Employee[Name] = _eply ), 'Fact'[Assignment FTE] ) ) )

Re: Employee FTE calculation

gottapu — Tue, 27 Oct 2020 05:18:58 GMT

wdx223_Daniel Thank you so much for all your help. I have used other DAX expression that you have provided and added rest all dimensions under filter condition so that no matter what users add to the report, FTE values are calculated correctly. The summarize expression that you have provided works for Employee FTE but when I slice, it does seem to be ignore those filters and provide me same values and so not optimal for me.

Once again, thanks much for taking your time to respond back quickly. I very much appreciate it.

Thanks,

-Raj

Re: Employee FTE calculation

wdx223_Daniel — Tue, 27 Oct 2020 06:21:29 GMT

gottapu if you have slicers in the model, you can try to change ALL('FACT') to ALLSELECT('FACT')