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 difference the cycle time is 2. I am having trouble getting the total to show the actual cycle time amount. Based on the values for this particular item it should be 22 but i am getting 211. I have no idea where that is coming from. There are only 19 rows for this 1 item. 15 of those rows show a value and 4 do not. Either way it comes out to 22 but it is showing 211.

 

This is the measure I am using to count cycle time. I think the discrepancy is coming from the empty rows, just a hunch.

 

Prod_CycleTime_Workday = SUMx(
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MIN( 'Cycle Time'[Production_Start date] )
            &&
        'Calendar'[Date] <= MAX( 'Cycle Time'[Production_End date] )
    ),
    'Calendar'[IsWorkdayNoHoliday]
)
 
 
Is something wrong with the measure that is getting me 211?
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.

3 Replies

  • 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?

  • ImkeF's avatar
    ImkeF
    Community Champion
    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.