How to calculate with values from pick list

Community Discussions
1

Hi all,

I'm trying to:

  1. Create a picklist with scores 0, 1 and 2 in MainTable, using PickSource as source for the picklist
  2. Sum the scores in MainTable

However, when I try to do any type of calculation on the values from the picklist in MainTable, I get a value error. In excel I would try to convert the 0, 1 and 2 columns to numbers, but I cannot find a similar functionality. Note that in the PickSource table I have no ussues calculating with the 0, 1 and 2s.

Is there a way to work around this?

Many thanks!

Best regards,
Benoit

2

Hi,

I'm still noch exactly sure what you try to do, but the first I think of is:
When you pick something from a picklist in a cell, this cell contains a row of the table the picklist is based on and not a value. That's why you always see the value in the first column in the cell (at least in the legacy version).

That's also why you can't just use the SUM() function, because you can't sum rows.

You can sum up filters like: =SUM(Filter(Tasks, "Tasks[Milestone]=%", "Work")[Points])
But only as mentioned if the Points column in this example contains values.

You can fake that with an extra column like "ScoreValues" with the formula:
=[scoreColumn][scoreColumnPickSource]

That will show the values like the Score column, but will contain values instead of rows that you can use for calculations.

I hope that helps
Johannes