Forum Discussion
Employee FTE calculation
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]))))
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
Community Championgottapu 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]
)
)
)
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
- wdx223_Daniel
gottapu if you have slicers in the model, you can try to change ALL('FACT') to ALLSELECT('FACT')