Calculated Column to Measure

Hi good day can anyone help me convert my calculated column into measure. I m having trouble on my report. Thank you in advance

%_Daily Plan = DIVIDE('Table01'[Overall_Cum_Plan_Hrs],'Table01'[Overall_Total_Column])

%_Actual = IF(ISBLANK(DIVIDE('Table01'[**bleep** Earned],'Table01'[Overall_Total_Column])),0, DIVIDE('Table01'[**bleep** Earned],'Table01'[Overall_Total_Column]))

Daily_Earned =
CALCULATE (
SUM ( 'TABLE PROGRESS'[Daily Earned] ),
FILTER (
ALL ( 'TABLE PROGRESS' ),
'TABLE PROGRESS'[Progress Date] = EARLIER ( 'Table 01'[PlanDate] )
&& 'TABLE PROGRESS'[Complex] = EARLIER ( 'Table 01'[Complex] )
&& 'TABLE PROGRESS'[Mode] = EARLIER ( 'Table 01'[Mode] )
)
)

Daily_Earned w/ Negative =
VAR Result =
CALCULATE (
SUM ( 'TABLE PROGRESS'[Daily Earned] ),
FILTER (
ALL ( 'TABLE PROGRESS' ),
'TABLE PROGRESS'[Progress Date] = EARLIER ( 'Table 01'[PlanDate] )
&& 'TABLE PROGRESS'[Complex] = EARLIER ( 'Table 01'[Complex] )
&& 'TABLE PROGRESS'[Mode] = EARLIER ( 'Table 01'[Mode] )

)
)
RETURN
IF ( Result < 0, 0, Result )

**bleep** Earned =
IF([PlanDate] <= TODAY(),
CALCULATE(
SUM('Table 01'[Daily_Earned]),
FILTER(
ALL('Table 01'),
'Table 01'[Complex] = EARLIER('Table 01'[Complex]) &&
'Table 01'[Mode] = EARLIER('Table 01'[Mode]) &&
'Table 01'[PlanDate] <= EARLIER('Table 01'[PlanDate]) &&
[PlanDate] <= TODAY()
)
)
)

Remaining = CALCULATE (
SUMX('MAIN TABLE', 'MAIN TABLE'[Remaining hours]),
FILTER (
ALL ( 'MAIN TABLE' ),
'MAIN TABLE'[Complex] = EARLIER ( 'Table 01'[Complex])&&
'MAIN TABLE'[Mode] = EARLIER ( 'Table 01'[Mode])
)
)

Overall_Total_Column =
CALCULATE (
SUM('Table 01'[Plan Hrs]),
FILTER (
ALL ( 'Table 01' ),
'Table 01'[Complex] = EARLIER('Table 01'[Complex]) &&
'Table 01'[Mode] = EARLIER('Table 01'[Mode])
)
)

Overall_Cum_Plan_Hrs = CALCULATE(
SUM('Table 01'[Plan Hrs]),
FILTER(
ALL('Table 01'),
'Table 01'[Complex] = EARLIER('Table 01'[Complex]) &&
'Table 01'[Mode] = EARLIER('Table 01'[Mode]) &&
'Table 01'[PlanDate] <= EARLIER('Table 01'[PlanDate])
)
)

Need Help

11 Replies

krishnakanth240
Super User
3 months ago
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)
- AllanBerces
  Post Prodigy
  3 months ago
  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
  - krishnakanth240
    Super User
    3 months ago
    Okay can you provide the logic for the other measures that you need
- Balqis_Adan
  Frequent Visitor
  3 months ago
  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 )
  - AllanBerces
    Post Prodigy
    3 months ago
    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
v-prasare
Community Support
3 months ago
Hi @AllanBerces,

As krishnakanth240 requested can you share pbix files. so, that this will allow the community to better understand your data model, relationships, and expected output, and provide a more accurate solution.

Thanks,

Prashanth
v-prasare
Community Support
3 months ago
Hi AllanBerces,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Balqis_Adan & krishnakanth240 ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
v-prasare
Community Support
3 months ago
Hi @AllanBerces,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support