Forum Discussion
Helper IIImplementing an Excel formula as a calculated column/measure
No prob - no need to apologize.
You're missing 1 step: "ID" (between #"Added Index" and SumID)
Having trouble reading your screenshots, but have the feeling that you first column-name (where I was expecting to see "H000018")is actually "ID". Then you would have the same problem like Sean and the code wouldn't work.
My solution would only work if you would skip the "ID" and take the column names like in row 2 of your Excel-screenshots.
Helper IISorry Sean, I wasn't clear, in cell A2 is a harcoded string ("H000018") that is also a column header, therefore what I am trying to achieve can be reformulated as follows:
=IF($B3="H000018",-1,0)+IF($C3="H000018",1,0)+IF(ISNUMBER(D2),D2,0)
=IF($B4="H000018",-1,0)+IF($C4="H000018",1,0)+IF(ISNUMBER(D3),D3,0)
=IF($B5="H000018",-1,0)+IF($C5="H000018",1,0)+IF(ISNUMBER(D4),D4,0)
... etc
please find attached screenshot.
Regards,
M.R.
Community ChampionMR2001 Okay we are getting there but still unclear now about ISNUMBER(D2)
Why do you start at D2 - again start from the Heading but this one is not fixed like $A$2?
If D2 says Calculation it will always give you 0 to start?
ImkeF does you solution work when A2 says H000018 (for me it only works when A2 says ID)
- ImkeF
Sean My solution only works if the column name of the first column is "H0000018" (and not ID). This seems to be the event that needs to be counted.
ID = List.First(Table.ColumnNames(#"Added Index")),
is the step that dynamically retrieves the tables column name (Table.ColumnNames) of the 1st column (List.First). I should better have used List.Range instead: You could have used it to retrieve the x-th column name from your tables, so it would have been more flexible.
You are right about the first row: My solution would count that event and not return a 0 like in Excel as you've figured out. (But this could be adjusted if needed).