Forum Discussion

TaroGulati's avatar
TaroGulati
Icon for Helper III rankHelper 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...
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]
)
rohit1991's avatar
rohit1991
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper III
    1 year ago

    rohit1991 thanks for the feedback. 

     

    unfortunately, it is still showing the wrong total: 

     

    • rohit1991's avatar
      rohit1991
      Icon for Super User rankSuper 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,