Dax SUMX not working because of fromatting

Your balance columns are actually text. It appears that the cells that look blank are not truly empty (perhaps they were pasted from somewhere). If you clear the cells that should be blank and refresh the table in the Power Pivot window, the fields should become decimal numbers that you can then use in measures.

Either select them manually and press delete (or use the Clear or Clear Contents options on the Home tab), or use a bit of code like this, after selecting all the data (including the value cells):

Please Login or Register  to view this content.

You could do it in power query if you have that. If not, just copy that code into a module in your workbook, then select the data and run that reallyClearCells macro (alt+f8 to bring up the macros dialog)