Calculated Column to Measure

[AllanBerces]

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 column i have trouble in changing to measure.

DailyPlan =

CALCULATE(

SUM('Table'[Plan Hrs]),

ALLEXCEPT('Table', 'Table'[PlanDate], 'Table'[Type] )

)

DailyEarned =

CALCULATE(

SUM('Table'[Overall_Earned w/_Negative]),

ALLEXCEPT('Table', 'Table'[PlanDate], 'Table'[Type] )

)

FOR CALCULATED COLUMN:

Overall(S)_Cum_Plan_Hrs = VAR CurrentDate = 'Table'[PlanDate]

                              VAR CurrentSubTask = 'Table'[Type]

                              VAR Filteredtable = FILTER('Table','Table'[PlanDate]<=CurrentDate && 'Table'[Type]= CurrentSubTask)

                              return

                              CALCULATE(SUM('Table'[Plan Hrs]), Filteredtable)

Overall(S)_Total_Column = SUMX(FILTER('Table','Table'[Type] =EARLIER('Table'[Type])),'Table'[Plan Hrs])

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

Overll(S)_Cum Earned = VAR CurrentDate = 'Table'[PlanDate]

                              VAR CurrentSubTask = 'Table'[Type]

                              VAR Filteredtable = FILTER('Table','Table'[PlanDate]<=CurrentDate && 'Table'[Type] = CurrentSubTask)

                              return

                                 IF('Table'[PlanDate]<=TODAY(),

                              CALCULATE(SUM('Table'[Overall(S)Daily Earned w/_Negative]), Filteredtable))

Copy of my file

https://docs.google.com/spreadsheets/d/12tT5WhGHFDWOOFZSbE2Tck-jeA05oLEp/edit?usp=sharing&ouid=10850...

Thank you

[Khushidesai0109 · Accepted solution]

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 · Accepted solution]

@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 · Accepted solution]

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 · Accepted solution]

@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 · Accepted solution]

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

[AllanBerces]

Hi @Khushidesai0109 @bhanu_gautam if you dont mine can pls help me also on this calculated column to convert into measure

WNTA_Combined_Earned =

IF(

    'Table'[Type] = "Overall",

    VAR WeekNum = 'Table'[PlanDate]

    RETURN

        SUMX(

            FILTER(

                'Table',

                'Table'[Type] IN {"Maintenance", "Project"} && 'Table'[PlanDate] = WeekNum

            ),

            'Table'[Overall_Earned w/_Negative]

        ),

    'Table'[Overall_Earned w/_Negative]

)

Thank you

[bhanu_gautam · Accepted solution]

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

[Khushidesai0109 · Accepted solution]

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!!

[AllanBerces]

Hi @Khushidesai0109 @bhanu_gautam thank you very much, very much appreciated.

[AllanBerces]

Hi @bhanu_gautam @Khushidesai0109 thank you very much for the help, working as i need.