Graphs for plotting time across Year

[AntBI26]

Hello all, I am having an issue about plotting time across different period. 

For example I want to have a bar chart of the overall time taken for all the different activities across the years. For date, I used the normal year from the date hierarchy, and for overall time this is calculated as below:

OverallTime1 =

VAR TotalSeconds=SUMX('Table1',HOUR('Table1'[Timetaken])*3600+MINUTE('Table1'[Timetaken])*60+SECOND('Table1'[Timetaken]))

VAR Days =TRUNC(TotalSeconds/3600/24)

VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)

VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)

VAR Secs = MOD(TotalSeconds,60)

return IF((Hors + (Days*24))<10,"0"&(Hors + (Days*24)),(Hors + (Days*24)))&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

When I drag both the Year and the Overall Time measure in the graph, I get a blank chart, and for some reasons, the Overalltime measure is by default placed in Tooltips.

Could someone help please?

[Anonymous · Accepted solution]

Hi @AntBI26 ,

Your side of the DAX returns a text type of data:

After testing, if you want to use a bar chart to display the total time, you can only convert the total time to seconds or minutes or hours, i.e. make it a number instead of hh:mm:ss.

Because it can only be automatically aggregated to sum if it is a numeric type, if you are using a text or Time type, it can only be automatically aggregated to count, which is obviously not the result you want.

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