Forum Discussion
Calculated Column to Measure
- 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
- 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
)
)
) - 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!! - 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])
)
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
- AllanBerces1 year agoPost Prodigy
Hi bhanu_gautam Khushidesai0109 thank you very much for the help, working as i need.
- AllanBerces1 year agoPost Prodigy
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]RETURNSUMX(FILTER('Table','Table'[Type] IN {"Maintenance", "Project"} && 'Table'[PlanDate] = WeekNum),'Table'[Overall_Earned w/_Negative]),'Table'[Overall_Earned w/_Negative])Thank you- Khushidesai01091 year agoSkilled Sharer
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!!- AllanBerces1 year agoPost Prodigy
Hi Khushidesai0109 bhanu_gautam thank you very much, very much appreciated.
- bhanu_gautam1 year agoSuper User
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])
)