DAX - how to combine 3 working measures to create a clean final measure

[Deevo_]

Hi everyone, 

I have 3 separate DAX measures. They all work fine in its current state, but want to clean it up. The second measure relies on the first meaure, then the 3rd measure relies on the 2nd measure, just like a hierarchy.

For the life of me, I am trying to combine them into one measure. I am having trouble with structuring them within one measure. Can someone please help me?

Here are my 3 measures which all work perfectly in its current state:

First Measure: --Sum total effort in hours

Project Effort Estimate (Hrs) = sum(Effort_Estimate[HoursEffortPerDay])

Second Measure: --Convert Sum of total Hours to FTE

Project Effort Estimate (FTE) =

VAR NumberofDaysInDate = CALCULATE ( COUNTROWS ( FILTER ( Dim_Date, Dim_Date[IsWorkDay] = TRUE() )))

VAR FullTimeFTE =

CALCULATE (
[Project Effort Estimate (Hrs)],
FILTER (Effort_Estimate, Effort_Estimate[employmentType] = "FullTime" )
) / NumberofDaysInDate / 7.5

VAR ContractorFTE =
CALCULATE (
[Project Effort Estimate (Hrs)],
FILTER (Effort_Estimate, Effort_Estimate[employmentType] = "Contractor" )
) / NumberofDaysInDate / 8

RETURN
FullTimeFTE + ContractorFTE

Measure 3: --Summarize the FTE totals

SUMX(
SUMMARIZE(
'Effort_Estimate',
Effort_Estimate[ID],
"TotalFTE", [Project Effort Estimate FTE Total]
),
[TotalFTE]
)

[rohit1991]

Hi @Deevo_ 

To clean it up and combine everything into a single final measure, you just need to inline the intermediate calculations inside your final SUMX measure.The final combined measure is given below:-

Total FTE =
SUMX (
    'Project Effort Estimate',
    VAR NumberOfDays =
        CALCULATE (
            COUNTROWS ( FILTER ( Dim_Date, Dim_Date[IsWorkDay] = TRUE () ) )
        )

    VAR FullTimeFTE =
        CALCULATE (
            'Project Effort Estimate'[ProjEffortHrs] * 'Project Effort Estimate'[HoursEffortPerDay],
            'Project Effort Estimate'[EmploymentType] = "FullTime"
        ) / NumberOfDays / 7.5

    VAR ContractorFTE =
        CALCULATE (
            'Project Effort Estimate'[ProjEffortHrs] * 'Project Effort Estimate'[HoursEffortPerDay],
            'Project Effort Estimate'[EmploymentType] = "Contractor"
        ) / NumberOfDays / 8

    RETURN
        FullTimeFTE + ContractorFTE
)

[Deevo_]

@rohit1991,

I appreciate your response Can you please help me to understand what these two measures are doing and what they mean?

'Project Effort Estimate'[ProjEffortHrs] * 'Project Effort Estimate'[HoursEffortPerDay],

[FBergamaschi · Accepted solution]

Meas =
SUMX (
SUMMARIZE (
'Effort_Estimate',
Effort_Estimate[ID],
"TotalFTE",
VAR NumberofDaysInDate =
CALCULATE ( COUNTROWS ( FILTER ( Dim_Date, Dim_Date[IsWorkDay] = TRUE () ) ) )
VAR FullTimeFTE =
CALCULATE (
SUM ( Effort_Estimate[HoursEffortPerDay] ),
FILTER ( Effort_Estimate, Effort_Estimate[employmentType] = "FullTime" )
) / NumberofDaysInDate / 7.5
VAR ContractorFTE =
CALCULATE (
SUM ( Effort_Estimate[HoursEffortPerDay] ),
FILTER ( Effort_Estimate, Effort_Estimate[employmentType] = "Contractor" )
) / NumberofDaysInDate / 8
RETURN
FullTimeFTE + ContractorFTE
),
[TotalFTE]
)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Deevo_]

@FBergamaschi Thank you so much. This structure is exactly what I was after. It works exactly as it should too. Easy to understand. Perfect!