Forum Discussion

TaroGulati's avatar
TaroGulati
Helper III
1 year ago
Solved

Time difference total issue

Hi all, 

I am having issue related to total for the below case:

In the below table i need to calculate the time difference:

For each category I have to identify last step based on time field. If last step is "X or Z" then i have to calculate difference of time between max time for step Z and min time for step X. If last step is "Y" then difference of time between max time for step Y and min time for step X. 

 

I created the below measure and it is working at the category level but not showing the correct total:

 

SWITCH (
    CALCULATE(VALUES('Table'[Step]),FILTER('Table','Table'[Time] = MAX('Table'[Time]))),
    "Y",
             DATEDIFF(CALCULATE (
                    MIN('Table'[Time]),
                   'Table'[Step] = "X"
                ),
                CALCULATE (
                    MAX( 'Table'[Time] ),
                    'Table'[Step] = "Y"
                ),SECOND),
    "X",
                DATEDIFF(CALCULATE (
                    MIN('Table'[Time]),
                   'Table'[Step] = "X"
                ),
                CALCULATE (
                    MAX( 'Table'[Time] ),
                    'Table'[Step] = "Z"
                ),SECOND),
               
    "Z",
                DATEDIFF(CALCULATE (
                    MIN('Table'[Time]),
                   'Table'[Step] = "X"
                ),
                CALCULATE (
                    MAX( 'Table'[Time] ),
                    'Table'[Step] = "Z"
                ),SECOND))
 
This is the result: 

As you can see total is not correct. 

 

does anyone know how to fix this?

Thanks

Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    alright i believe this should solve your issue. 

    TimeDifference =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Category],
        "TimeDiff",
            SWITCH (
                CALCULATE (
                    VALUES('Table'[Step]),
                    FILTER('Table', 'Table'[Time] = MAX('Table'[Time]))
                ),
                "Y",
                    DATEDIFF (
                        CALCULATE (
                            MIN('Table'[Time]),
                            'Table'[Step] = "X"
                        ),
                        CALCULATE (
                            MAX('Table'[Time]),
                            'Table'[Step] = "Y"
                        ),
                        SECOND
                    ),
                "X",
                    DATEDIFF (
                        CALCULATE (
                            MIN('Table'[Time]),
                            'Table'[Step] = "X"
                        ),
                        CALCULATE (
                            MAX('Table'[Time]),
                            'Table'[Step] = "Z"
                        ),
                        SECOND
                    ),
                "Z",
                    DATEDIFF (
                        CALCULATE (
                            MIN('Table'[Time]),
                            'Table'[Step] = "X"
                        ),
                        CALCULATE (
                            MAX('Table'[Time]),
                            'Table'[Step] = "Z"
                        ),
                        SECOND
                    )
            )
    ),
    [TimeDiff]
)

6 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    hi TaroGulati ,

    Your measure is calculating correctly at the category level, but the total is incorrect because Power BI does not sum up row-level calculations for totals in a measure using CALCULATE. Instead, it evaluates the formula in the context of all data, which often leads to incorrect totals.

    Fixing the Total Calculation

    To ensure the correct total, modify your measure to sum up the values at the row level rather than relying on implicit aggregation:

    Corrected Measure

    Total Time Difference =
SUMX(
    VALUES('Table'[Category]),
    SWITCH (
        CALCULATE(VALUES('Table'[Step]),FILTER('Table','Table'[Time] = MAX('Table'[Time]))),
        "Y",
                 DATEDIFF(
                    CALCULATE ( MIN('Table'[Time]), 'Table'[Step] = "X" ),
                    CALCULATE ( MAX('Table'[Time]), 'Table'[Step] = "Y" ),
                    SECOND
                ),
        "X",
                 DATEDIFF(
                    CALCULATE ( MIN('Table'[Time]), 'Table'[Step] = "X" ),
                    CALCULATE ( MAX('Table'[Time]), 'Table'[Step] = "Z" ),
                    SECOND
                ),
        "Z",
                 DATEDIFF(
                    CALCULATE ( MIN('Table'[Time]), 'Table'[Step] = "X" ),
                    CALCULATE ( MAX('Table'[Time]), 'Table'[Step] = "Z" ),
                    SECOND
                )
    )
)

     

    • TaroGulati's avatar
      TaroGulati
      Helper III
      1 year ago

      rohit1991 thanks for the feedback. 

       

      unfortunately, it is still showing the wrong total: 

       

      • rohit1991's avatar
        rohit1991
        Super User
        1 year ago

        Please cross check for any filters on the page or the visual.
        If it still continues to persist. Please share the file in DM if possible so i can have  a  look at it,