cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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)

 

HR data.png

 

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 NameAssignment IDAssignment FTEEmployee FTE
A1111
B210.50.7
B220.20.7
C310.30.7
C320.10.7
C330.30.7

 

 

Thank you,

-Raj

12 REPLIES 12
Highlighted

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

 

Highlighted

@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

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors