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 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=108506264278584806223&rtpof=true&sd=true

Thank you

Need Help

Khushidesai0109

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

7 Replies

Khushidesai0109
Skilled Sharer
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
- AllanBerces
  Post Prodigy
  1 year ago
  Hi bhanu_gautam Khushidesai0109 thank you very much for the help, working as i need.
- AllanBerces
  Post Prodigy
  1 year ago
  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
  - Khushidesai0109
    Skilled Sharer
    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
Super User
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
)
)
)

Forum Discussion

Calculated Column to Measure

Cumulative Plan Hours Measure

Total Plan Hours Per Type Measure

Cumulative Earned Measure

7 Replies

Cumulative Plan Hours Measure

Total Plan Hours Per Type Measure

Cumulative Earned Measure

Related content

Measure or Calculated Column

TMDL - calculated column

DAX Calculated Measure

Convert calculated column into measure

Write logic for Calculated Column or Measure