Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
3 months ago

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'[Ove...
Need Help
krishnakanth240's avatar
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's avatar
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's avatar
    krishnakanth240
    Super User
    3 months ago

    Okay can you provide the logic for the other measures that you need

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      3 months ago

      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

      • krishnakanth240's avatar
        krishnakanth240
        Super User
        3 months ago

        Okay, can you share pbix file link over a onedrive with providing access to it.

        1.Can you explain in more detail the requirement with respect to Data Modelling.

        2.From requirements part, what are the logics you need as output from the measures