Forum Discussion

AntBI26's avatar
AntBI26
Frequent Visitor
2 years ago
Solved

Graphs for plotting time across Year

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?
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.