Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sum up the difference in Maximum and minimum value per meter group

Please assist me with a measure to calculate the total hours in the below table using the logic below. i used the dax below , but my subtotal in the table is not summing up.

 

SMU Hrs = IFERROR( CALCULATE(MAX(EngineReading[StopReading])-MIN(EngineReading[StartReading]),EngineReading[MeterID]="HRS")+CALCULATE(MAX(EngineReading[StopReading])-MIN(EngineReading[StartReading]),EngineReading[MeterID]="HRS1"),0)

 

Total Hours = Difference between Maximum end reading and minimum start reading when meter is HRS ,Difference between Maximum end reading and minimum start reading when meter is HRS1.

 

 

 

       
Shift TypeShift DateEquip IDMeterStart HoursEnd ReadingDifference
Day Shift22-10-2019DR770HRS1152.50156.804.30
Night Shift22-10-2019DR770HRS1157.00161.604.60
Day Shift23-10-2019DR770HRS1161.70167.105.40
Night Shift23-10-2019DR770HRS1167.10172.405.30
Day Shift24-10-2019DR770HRS1172.40178.706.30
Night Shift24-10-2019DR770HRS1178.70184.205.50
Day Shift25-10-2019DR770HRS1184.30189.104.80
Day Shift28-10-2019DR770HRS1189.50193.103.60
Night Shift28-10-2019DR770HRS1193.10197.704.60
Day Shift29-10-2019DR770HRS1197.70202.204.50
Night Shift29-10-2019DR770HRS1202.20208.105.90
Day Shift30-10-2019DR770HRS1208.20218.2010.00
Night Shift30-10-2019DR770HRS1218.20228.7010.50
Day Shift31-10-2019DR770HRS1228.80232.003.20
Night Shift6-10-2019DR770HRS2319.102321.602.50
Day Shift7-10-2019DR770HRS2321.602330.809.20
Night Shift7-10-2019DR770HRS2330.802338.007.20
Day Shift8-10-2019DR770HRS2338.002341.203.20
Day Shift9-10-2019DR770HRS2341.202345.704.50
Night Shift9-10-2019DR770HRS2345.702349.704.00
Day Shift10-10-2019DR770HRS2349.702353.403.70
Day Shift11-10-2019DR770HRS2353.402359.105.70
Night Shift11-10-2019DR770HRS2359.102364.605.50
Night Shift12-10-2019DR770HRS2364.602370.606.00
Day Shift13-10-2019DR770HRS2370.602379.909.30
Night Shift13-10-2019DR770HRS2379.902389.509.60
Day Shift14-10-2019DR770HRS2389.502396.406.90
Night Shift14-10-2019DR770HRS2396.402401.204.80
Day Shift15-10-2019DR770HRS2401.202409.007.80
Night Shift15-10-2019DR770HRS2409.002418.909.90
Day Shift16-10-2019DR770HRS2418.902428.409.50
Night Shift16-10-2019DR770HRS2428.402437.409.00
Day Shift17-10-2019DR770HRS2437.402444.006.60
Night Shift17-10-2019DR770HRS2444.002446.602.60
Night Shift18-10-2019DR770HRS2446.602455.108.50
Day Shift21-10-2019DR770HRS2455.102460.905.80
Night Shift21-10-2019DR770HRS2460.902464.003.10

11 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    You can achieve this as follows:

    1. Create 2 measures. One for maximum End Reading, one for minimum Start Reading.

        Max End Reading = MAX([End Reading])

        Min Start Reading = MIN([Start Reading])

     

    2. Add a calculation as follows for Total Hours:

        Total Hours = IF([Meter] = "HRS" || [Meter] = "HRS1", [Max End Reading] - [Min Start Reading], 0)

     

    Let me know if this works.

     

    Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Pragati11,

     

    Thanks for the reply

     

    1. Ist measure creatly successfuly,

     

    2. 2nd Measure is not working, as i am unable to select [Meter] when i use IF.

     

    Thanks

    • Pragati11's avatar
      Pragati11
      Icon for Super User rankSuper User
      6 years ago

      Hi Anonymous ,

       

      The 2nd one should be your column, that uses the 2 measures.

       

      Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi Pragati11,

      what will be the logical logical test for mth IF .

       

      thanks 

      • Pragati11's avatar
        Pragati11
        Icon for Super User rankSuper User
        6 years ago

        Anonymous Didn't understand the question.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    If you want column then use below dax.

     

    Diff=IF(table[Meter] in {"HRS","HRS1"},Table[Endtime]-Table[StartTime],0)

     

    If you want measure

    Diff=Diff=IF(Min(table[Meter]) in {"HRS","HRS1"},min(Table[Endtime])-min(Table[StartTime]),0)

     

    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.