Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Total Value incorrect with measure

Hi All, 

 

I have come across an issue where my Time Spent Column is showing an incorrect total, as you can see from the Time column there are some big numbers showing and the total should not equal to 11 hours it should be more. The Time spent times are correct for the ID numbers I just need to get the total amount correct

 

 

The measure I used to calculate the Time Spent is below.. Is someone able to advise me on how I can get the exact total amount to show?- 

 

total_seconds_dax =

var total_second =
SUMX(
'New - Timesheet 2021',
INT(
MID(
'New - Timesheet 2021'[Time Spent],
1,
SEARCH(":",'New - Timesheet 2021'[Time Spent],1,0) - 1
)
)
) * 60 * 60
+
SUMX(
'New - Timesheet 2021',
INT(
MID(
'New - Timesheet 2021'[Time Spent],
SEARCH(":",'New - Timesheet 2021'[Time Spent],1,0) + 1,
SEARCH(
":",
'New - Timesheet 2021'[Time Spent],
SEARCH(":",'New - Timesheet 2021'[Time Spent],1,0) + 1
,0
)
-
(SEARCH(":",'New - Timesheet 2021'[Time Spent],1,0) + 1)
)
)
) * 60
+
SUMX(
'New - Timesheet 2021',
INT(
MID(
'New - Timesheet 2021'[Time Spent],
SEARCH(
":",
'New - Timesheet 2021'[Time Spent],
SEARCH(":",'New - Timesheet 2021'[Time Spent],1,0) + 1
,0
) + 1
,
LEN('New - Timesheet 2021'[Time Spent]) -
SEARCH(
":",
'New - Timesheet 2021'[Time Spent],
SEARCH(":",'New - Timesheet 2021'[Time Spent],1,0) + 1
,0
)
)
)
)

// var DayCount = INT(total_second/(24*60*60))
var HoursCount = MOD(INT(total_second/(60*60)),24)
var MinCount = MOD(INT(total_second/60),60)
var SecCount = MOD(total_second,60)

RETURN HoursCount & " Hours & " & MinCount & " Minutes & " & SecCount & " Seconds"

 

Thanks all 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  Anonymous ,

    I created the data and simulated your scenario:

    Here are the steps you can follow：

    You can consider Hour, Minutes, Seconds to use HASONEVALUE to total the total, and finally combine them together

    1. Create measure.

    Hour =
var _hour =DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),HOUR)
VAR __table = SUMMARIZE('Table',[ID Numbers],"__value",_hour)
RETURN
IF(HASONEVALUE('Table'[ID Numbers]),_hour,SUMX(__table,[__value]))
    Minute =
var _hour =DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),HOUR)
var _min=DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),MINUTE)-_hour *60
VAR __table = SUMMARIZE('Table',[ID Numbers],"__value",_min)
RETURN
IF(HASONEVALUE('Table'[ID Numbers]),_min,SUMX(__table,[__value]))
    Seconds =
var _hour =DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),HOUR)
var _min=DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),MINUTE)-_hour *60
var _sec=DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),SECOND) -_hour * 3600 - _min*60
VAR __table = SUMMARIZE('Table',[ID Numbers],"__value",_sec)
RETURN
IF(HASONEVALUE('Table'[ID Numbers]),_sec,SUMX(__table,[__value]))
    correct_time =
[Hour]&" Hours "&[Minute]&" Minutes "&[Seconds]&" Seconds "&""

    2. Result:

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous ,

    I created the data and simulated your scenario:

    Here are the steps you can follow：

    You can consider Hour, Minutes, Seconds to use HASONEVALUE to total the total, and finally combine them together

    1. Create measure.

    Hour =
var _hour =DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),HOUR)
VAR __table = SUMMARIZE('Table',[ID Numbers],"__value",_hour)
RETURN
IF(HASONEVALUE('Table'[ID Numbers]),_hour,SUMX(__table,[__value]))
    Minute =
var _hour =DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),HOUR)
var _min=DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),MINUTE)-_hour *60
VAR __table = SUMMARIZE('Table',[ID Numbers],"__value",_min)
RETURN
IF(HASONEVALUE('Table'[ID Numbers]),_min,SUMX(__table,[__value]))
    Seconds =
var _hour =DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),HOUR)
var _min=DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),MINUTE)-_hour *60
var _sec=DATEDIFF(MAX('Table'[Date2]),MAX('Table'[Date1]),SECOND) -_hour * 3600 - _min*60
VAR __table = SUMMARIZE('Table',[ID Numbers],"__value",_sec)
RETURN
IF(HASONEVALUE('Table'[ID Numbers]),_sec,SUMX(__table,[__value]))
    correct_time =
[Hour]&" Hours "&[Minute]&" Minutes "&[Seconds]&" Seconds "&""

    2. Result:

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly