Calculate and display the sum of total hours rather than the count of each hour record

[Anonymous]

I have a table that looks like this:

Location | Affected System | Total Hours

USA        |  ERP                    | 45

Ireland    | ERP                     | 20

USA        | SAP                     | 12

Japan      | LCV                     | 23

Each Total Hours value represents how long a system has been down for in that location. I wish to display, using a bar chart, the total amount of hours the system has been down, not the count of each total hours record per location

Is there a way to do this within Power BI? I have searched it but my issue seems to be too specific. Any help would be appreciated. Thanks!

[HotChilli · Accepted solution]

If you want to SUM the Total Hours by the System, create a measure

Measure  = CALCULATE(SUM(Table[Total Hours]), ALLEXCEPT(Table, Table[System]))

It's always better if you can provide sample data (thanks for that) and desired result.

[Anonymous]

Thank you @HotChilli ,

This seems to work except I have an error saying: "The function SUM cannot work with values of type String."

I'm aware that VALUE() converts strings to a number, but it doesn't seem to work for converting an entire column of type string to a number. Any idea what the correct syntax of this would be? 

Thanks again.

[HotChilli]

What's the data type on Hours? It should be a whole number

[Anonymous]

It's from a SharePoint list with type Short Text. My bad, should have made it a number from the start. Do you recommend changing the column data type or converting to a new type within the measure?

EDIT: I changed the column type to a number and it's still throwing the same error. Any ideas as to where I have gone wrong? Thanks

[HotChilli]

I don't know where the error is but we should be able to debug.

Create a simple measure :

SUM(Table[Total Hours])

and see if that works first

[Anonymous]

I solved the issue, in the datasheet view, the TotalHours column was considered a String. I went to Modelling > Data type and changed the type to a whole number. As you said before. 

Thanks for your help! Very helpful. 

[ZunzunUOC]

Hi @Anonymous , you need both x and y to draw a bar chart. Which are they? I just see total hours