Time difference total issue

[TaroGulati]

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

[rohit1991]

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]

@rohit1991 thanks for the feedback. 

unfortunately, it is still showing the wrong total: 

[rohit1991]

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,

[TaroGulati]

Hi @rohit1991 you can find the file at this link: 

Demo.pbix

[rohit1991 · Accepted solution]

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]
)

[TaroGulati]

Yes, this works. I understood what i was missing. Thanks