Forum Discussion
Calculated Column to Measure
Hi AllanBerces
Can you try these measures
Daily Earned =
CALCULATE(SUM('TABLE PROGRESS'[Daily Earned]),'TABLE PROGRESS'[Progress Date] = MAX('Table 01'[PlanDate]),'TABLE PROGRESS'[Complex] = SELECTEDVALUE('Table 01'[Complex]),'TABLE PROGRESS'[Mode] = SELECTEDVALUE('Table 01'[Mode]))
Daily Earned (Neg) =
VAR Result = CALCULATE(SUM('TABLE PROGRESS'[Daily Earned]),'TABLE PROGRESS'[Progress Date] = MAX('Table 01'[PlanDate]),'TABLE PROGRESS'[Complex] = SELECTEDVALUE('Table 01'[Complex]),'TABLE PROGRESS'[Mode] = SELECTEDVALUE('Table 01'[Mode]))
RETURN
IF(Result < 0, 0, Result)
Cumulative Earned =
VAR CurrDate = MAX('Table 01'[PlanDate])
RETURN
IF(CurrDate <= TODAY(), CALCULATE(SUMX(
VALUES('Table 01'[PlanDate]),[Daily Earned]),
'Table 01'[PlanDate] <= CurrDate,'Table 01'[Complex] = SELECTEDVALUE('Table 01'[Complex]),'Table 01'[Mode] = SELECTEDVALUE('Table 01'[Mode])))
Overall Total =
CALCULATE(SUM('Table 01'[Plan Hrs]),
ALLEXCEPT('Table 01', 'Table 01'[Complex], 'Table 01'[Mode]))
Cumulative Plan Hrs =
VAR CurrDate = MAX('Table 01'[PlanDate])
RETURN
CALCULATE(SUM('Table 01'[Plan Hrs]),'Table 01'[PlanDate] <= CurrDate, ALLEXCEPT('Table 01', 'Table 01'[Complex], 'Table 01'[Mode]))
Remaining =
CALCULATE(SUM('MAIN TABLE'[Remaining hours]),
ALLEXCEPT('MAIN TABLE', 'MAIN TABLE'[Complex], 'MAIN TABLE'[Mode]))
% Daily Plan =
DIVIDE([Cumulative Plan Hrs], [Overall Total])
% Actual =
DIVIDE([Cumulative Earned], [Overall Total], 0)
Hi krishnakanth240 thank you for the reply,but for all the measure you provided only this measure give correct data. Daily Earned and Daily Earned (Neg). The rest are incorect
Okay can you provide the logic for the other measures that you need
krishnakanth240 thank you very much for the reply, below is my logic. Table 01 and Main Table are connected to bridge table complex and Mode,. Table 01 and Table Progress are connected to Calendar Table
Thank you
the main challenge here is replacing the EARLIER function with Variables (VAR) to capture the current context of the row. Also, ensure you use MAX or SELECTEDVALUE to identify the current Date, Complex, and Mode within your Measure. This will make your report much faster than using Calculated Columns
Cumulative Plan Hrs
Overall_Cum_Plan_Hrs_Measure = VAR CurrentDate = MAX('Table 01'[PlanDate]) VAR CurrentComplex = SELECTEDVALUE('Table 01'[Complex]) VAR CurrentMode = SELECTEDVALUE('Table 01'[Mode]) RETURN CALCULATE( SUM('Table 01'[Plan Hrs]), FILTER( ALL('Table 01'), 'Table 01'[Complex] = CurrentComplex && 'Table 01'[Mode] = CurrentMode && 'Table 01'[PlanDate] <= CurrentDate ) ) Overall Total Overall_Total_Measure = VAR CurrentComplex = SELECTEDVALUE('Table 01'[Complex]) VAR CurrentMode = SELECTEDVALUE('Table 01'[Mode]) RETURN CALCULATE ( SUM('Table 01'[Plan Hrs]), FILTER ( ALL ( 'Table 01' ), 'Table 01'[Complex] = CurrentComplex && 'Table 01'[Mode] = CurrentMode ) ) %_Daily Plan %_Daily Plan_Measure = DIVIDE( [Overall_Cum_Plan_Hrs_Measure], [Overall_Total_Measure], 0 )Hi Balqis_Adan krishnakanth240 v-prasare thank you very much for the reply, but have some concern it work on the Table but when i change to Matrix it not show the Total in collaps and expand
possible to make something like this
Thank you
Hi Balqis_Adan krishnakanth240 v-prasare thank you very much for the reply, but have some concern it work on the Table but when i change to Matrix it not show the Total in collaps and expand
possible to make something like this
Thank you
