Calculate DAX measure for row total by ignoring strings

Question

looking for a help to calculate total on each row by ignoring strings at power bi (DAX) 

I need to add few column values which is a combination of number & text and show the sum in the column "Total" as shown in the here (Expected result)

the condition i required is, if any of the value in the respective rows has text value then I need to avoid it and add the remaining only numbers

SAMPLE DATA

Expected result

Tried the below measure just for one column but since am using filter, its eliminating the rows which contain SL but i required remaining data totals just by ignoring particular strings(cell value)

Measure = 
var data = SUMX(FILTER(Append1, Append1[7/6/2023]<>"SL"), VALUE(Append1[7/6/2023]))
    
return data

looking for suggestions!!

Answer 1

Do following steps in Power Query

1. First Unpivot columns other than Name to form Name, Date, Value columns
2. Delete top seven rows
3. Write a custom column to replace if text presents then with zero

custom = if Value.Is([Value],type number) then [Value] else 0)

4. Then Group by Name to display Total for each Name.

Here is M code

let
    Source = Excel.Workbook(File.Contents("C:\Ashok\Power BI\Stack Overflow\Data18jul23.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"5/22/2023", type any}, {"5/23/2023", type any}, {"5/24/2023", type any}, {"5/25/2023", type any}, {"5/26/2023", type any}, {"5/27/2023", type text}, {"5/28/2023", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name"}, "Attribute", "Value"),
    #"Removed Top Rows" = Table.Skip(#"Unpivoted Other Columns",7),
    #"Added Custom" = Table.AddColumn(#"Removed Top Rows", "Custom", each if Value.Is([Value],type number) then [Value] else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Name"}, {{"Total", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"