Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Calculated Column to Measure

Hi, Can anyone help me on how can i convert my calculated column into a measure. The main objective is to get the Daily %_Plan and %_Actual. I have measure for Planhrs and DailyEarned but other colu...
  • Khushidesai0109's avatar
    1 year ago

    Hiii   AllanBerces 

    Cumulative Plan Hours Measure

     
    Overall_Cum_Plan_Hrs = 
    VAR CurrentDate = MAX('Table'[PlanDate])
    VAR CurrentSubTask = SELECTEDVALUE('Table'[Type])
    RETURN
    CALCULATE(
    SUM('Table'[Plan Hrs]),
    'Table'[PlanDate] <= CurrentDate,
    'Table'[Type] = CurrentSubTask
    )

     

     

    Total Plan Hours Per Type Measure

     
     
    Overall_Total_Plan_Hrs = 
    CALCULATE(
    SUM('Table'[Plan Hrs]),
    ALLEXCEPT('Table', 'Table'[Type])
    )

    Daily Plan % Measure
    Overall_Percent_Daily_Plan =
    DIVIDE(
    [Overall_Cum_Plan_Hrs],
    [Overall_Total_Plan_Hrs]
    )

     

    Cumulative Earned Measure

    Overall_Cum_Earned = 
    VAR CurrentDate = MAX('Table'[PlanDate])
    VAR CurrentSubTask = SELECTEDVALUE('Table'[Type])
    RETURN
    CALCULATE(
    SUM('Table'[Overall_Earned w/_Negative]),
    'Table'[PlanDate] <= CurrentDate,
    'Table'[Type] = CurrentSubTask
    )

     

    Did I answer your question? Mark my post as a solution! And Kudos are appreciated

  • bhanu_gautam's avatar
    1 year ago

    AllanBerces Here’s how you can convert each of your calculated columns into measures

     

    Overall(S)_Cum_Plan_Hrs:

    Overall(S)_Cum_Plan_Hrs =
    VAR CurrentDate = MAX('Table'[PlanDate])
    VAR CurrentSubTask = MAX('Table'[Type])
    RETURN
    CALCULATE(
    SUM('Table'[Plan Hrs]),
    FILTER(
    ALL('Table'),
    'Table'[PlanDate] <= CurrentDate && 'Table'[Type] = CurrentSubTask
    )
    )

     

    Overall(S)_Total_Column =
    CALCULATE(
    SUM('Table'[Plan Hrs]),
    ALL('Table'[PlanDate])
    )

     

    Overall_%_Daily Plan =
    DIVIDE(
    [Overall(S)_Cum_Plan_Hrs],
    [Overall(S)_Total_Column]
    )

     

    Overll(S)_Cum Earned =
    VAR CurrentDate = MAX('Table'[PlanDate])
    VAR CurrentSubTask = MAX('Table'[Type])
    RETURN
    IF(
    CurrentDate <= TODAY(),
    CALCULATE(
    SUM('Table'[Overall_Earned w/_Negative]),
    FILTER(
    ALL('Table'),
    'Table'[PlanDate] <= CurrentDate && 'Table'[Type] = CurrentSubTask
    )
    )
    )

  • Khushidesai0109's avatar
    Khushidesai0109
    1 year ago


    WNTA_Combined_Earned_Measure =
    VAR WeekNum = SELECTEDVALUE('Table'[PlanDate])
    RETURN
    SUMX(
    FILTER(
    ALL('Table'),
    'Table'[Type] IN {"Maintenance", "Project"} &&
    'Table'[PlanDate] = WeekNum
    ),
    'Table'[Overall_Earned w/_Negative]
    ) +
    IF(
    SELECTEDVALUE('Table'[Type]) = "Overall",
    0,
    SELECTEDVALUE('Table'[Overall_Earned w/_Negative])
    )

    Here you go!!


    Kuddos are appreciated!!

  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    AllanBerces Try using

    DAX
    WNTA_Combined_Earned_Measure =
    IF(
    SELECTEDVALUE('Table'[Type]) = "Overall",
    VAR WeekNum = SELECTEDVALUE('Table'[PlanDate])
    RETURN
    SUMX(
    FILTER(
    'Table',
    'Table'[Type] IN {"Maintenance", "Project"} && 'Table'[PlanDate] = WeekNum
    ),
    'Table'[Overall_Earned w/_Negative]
    ),
    SUM('Table'[Overall_Earned w/_Negative])
    )