Time Measure sum Time Measure error

[Anonymous]

Hi Guys,

I'm trying to sum the three below measures together, which forum member Icey kindly help with,  but an recieving the following error.

Couldnt load the data for the visual - Calculation error cannot convert value of Type text to type number

MY measure which i am recieivng the error from is

Total activity new measure = CALCULATE('Calls SQL'[Total call time new mesaure] + 'Calls SQL'[Total wrap time new measure] + Breaks[Total make busy time new measure])

Measure 1

Total call time new mesaure = VAR Seconds1 = SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Total Time on Calls] ) ) VAR Minutes1 = SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Total Time on Calls] ) ) VAR Hours1 = SUMX ( 'Calls SQL', HOUR ( 'Calls SQL'[Total Time on Calls] ) ) VAR Seconds2 = MOD ( Seconds1, 60 ) VAR Minutes2 = MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 ) VAR Hours2 = Hours1 + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 ) RETURN Hours2 & ":" & FORMAT ( Minutes2, "00" ) & ":" & FORMAT ( Seconds2, "00" )

Measure 2

Total wrap time new measure = VAR Seconds1 = SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Wrap Time] ) ) VAR Minutes1 = SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Wrap Time] ) ) VAR Hours1 = SUMX ( 'Calls SQL', HOUR ( 'Calls SQL'[Wrap Time] ) ) VAR Seconds2 = MOD ( Seconds1, 60 ) VAR Minutes2 = MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 ) VAR Hours2 = Hours1 + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 ) RETURN Hours2 & ":" & FORMAT ( Minutes2, "00" ) & ":" & FORMAT ( Seconds2, "00" )

Measure 3

Total make busy time new measure = VAR Seconds1 = SUMX ( 'Breaks', SECOND ( 'Breaks'[Minutes] ) ) VAR Minutes1 = SUMX ( 'Breaks', MINUTE ( 'Breaks'[Minutes] ) ) VAR Hours1 = SUMX ( 'Breaks', HOUR ( 'Breaks'[Minutes] ) ) VAR Seconds2 = MOD ( Seconds1, 60 ) VAR Minutes2 = MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 ) VAR Hours2 = Hours1 + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 ) RETURN Hours2 & ":" & FORMAT ( Minutes2, "00" ) & ":" & FORMAT ( Seconds2, "00" )

[v-shex-msft]

Hi @Anonymous,

Current DAX formulas do not support to use math operator on text values.
According to your DAX formulas, they will return merges text values that not able to do math calculation, you can only use text operation functions and logic operators to compare with different text values.

DAX operators 

Please keep your duration as a numeric value(total second) and convert them after calculations instead convert before calculations.

Aggregating Duration Time 

Regards,

Xiaoxin Sheng

[Anonymous]

Ok that makes sense thanks for that, could you help with the formula??

[v-shex-msft]

HI @Anonymous ,

You can try to use the following measure formula:

Total time =
VAR tsCS =
    SUMX (
        'Calls SQL',
        VAR _t1 =
            TIMEVALUE ( 'Calls SQL'[Total Time on Calls] )
        VAR _t2 =
            TIMEVALUE ( 'Calls SQL'[Wrap Time] )
        RETURN
            ( HOUR ( _t1 ) + HOUR ( _t2 ) ) * 3600
                + ( MINUTE ( _t1 ) + MINUTE ( _t2 ) ) * 60
                + SECOND ( _t1 )
                + SECOND ( _t2 )
    )
VAR tsBK =
    SUMX (
        'Breaks',
        VAR _t1 =
            TIMEVALUE ( 'Breaks'[Minutes] )
        RETURN
            HOUR ( _t1 ) * 3600
                + MINUTE ( _t1 ) * 60
                + SECOND ( _t1 )
    )
VAR ts = tsCS + tsBK
VAR _hour =
    INT ( ts / 3600 )
VAR _minute =
    INT ( MOD ( ts, 3600 ) / 60 )
VAR _second = ts - _hour * 3600 - _minute * 60
RETURN
    _hour & ":"
        & FORMAT ( _minute, "00" ) & ":"
        & FORMAT ( _second, "00" )

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for this @v-shex-msft .  I seem to be getting different results to what excel would show as a total. 

Excel = 48:47:31

Power BI Measure = 499:47:08

[v-shex-msft · Accepted solution]

Hi @Anonymous,

I found a typo on my formula in 'tsBK' variable('Calls SQL' should be 'Breaks' and I already modify on above post), you can modify measure formula to confirm if the wrong calculation is caused with this part.

Regards,

Xiaoxin Sheng

[Anonymous]

Amazing amazing amazing thanks very much