Forum Discussion

AA622's avatar
AA622
Helper II
3 years ago
Solved

Total is not adding up correctly

Hello   I am trying to calculate total batch time. Please refer to the image below.   The column named Cycle time shows the amount of days between start date + end date. Even if its 1 day d...
Need Help
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi AA622 ,
    a possible solution could look like so:

    Measure = 
SUMX (
    SUMMARIZE ( 'Table', 'Table'[ItemID], 'Table'[ProductionID] ),
    VAR _StartTime =
        CALCULATE ( MIN ( 'Table'[StartTime] ) )
    VAR _EndTime =
        CALCULATE ( MAX ( 'Table'[EndTime] ) )
    RETURN
        CALCULATE (
            SUM ( 'Calendar'[IsWorkdayNoHoliday] ),
            'Calendar'[Date] >= _StartTime,
            'Calendar'[Date] <= _EndTime
        )
)

    Next time, please provide sample data in a usable form.
    Please also see file attached.

ImkeF's avatar
ImkeF
Community Champion
3 years ago

Hi AA622 ,
you are probably iterating the wrong table here: At the total-level, there will be only one value for the MIN( 'Cycle Time'[Production_Start date] ) and MAX( 'Cycle Time'[Production_Start date] ). Those will be the earliest and latest dates for ALL start dates in your CycleTime-table. This will basically remove all filters on the iterated items and cause the number to be that high.

Instead, you should iterate a table that contains the ItemID and ProductionsID-fields. (Hard to be more specific here without seeing the details of your data model).
Then, make sure to use a CALCULATE around the 'Calendar'[IsWorkdayNoHoliday] , to cover the effects of the row context within iterators: CALCULATE('Calendar'[IsWorkdayNoHoliday]) 

  • AA622's avatar
    AA622
    Helper II
    3 years ago

    Hello

     

    What more details do you need? All the fields for this particular report are housed within the same table from a SQL query join. Item ID and productionID are in the same table. You can call it Cycle Time if that helps.

     

    I understand that it needs to iterate from the ItemId/Production ID but I am having trouble structuring the new measure. Any insight?