EnumList to calculate a SUM

jimdumont · 11-03-2019 06:23 AM

Hello!

I have a form with a column displaying an EnumList referenced in an other table. The user can select as many items as he wants from that list and I want a second column to calculate a SUM of numbers associated (row) with these choice located in my referenced table.

Also, I would like to display that EnumList later on on a detail view with one item per line (line break) instead of all in the same line separated by commas.

Thank you!

Steve

Assuming the EnumList has a base type of Ref, you might be able to do this:

SUM([EnumListOfRef][Numeric])

jimdumont

The two tables are not referenced together with a key. I use Enumlist with the VALID_IF data validity with SELECT(Table2[Description]) to populate the choices. It works but return a LIST of selected [Descritpion].

Now I need to add Table2[Numeric] from the corresponding rows that were selected in the previous column.

When I use Ref as the base type, it doesn’t work.

Steve

In what way doesn’t it work?

jimdumont

I got it to work. I was’n using the key column for my Table2, However, since I use Select, the SUM function as you proposed was not available. The result of my first query return a List of key columns showing the labels, so to be able to pull the numeric value of the other column for this selection I used:

SUM(SELECT(Table2[Numeric], CONTAINS([EnumListofReference],[Table2Label])))

Not sure if there’s something I’m doing wrong…

kloshar4o

Thank you, this helped me a lot

IjeomaO

@jimdumont Thank you so much, sometimes after you have spent hours working on an app, you don't have the mind capacity to formulate expressions. This is what I needed exactly for a sum expression.

jimdumont

Is it possible that I can’t use the expression SELECT in my EnumList? Here’s what is says in the formula validation box:

“Column A1 is used in a SELECT expression. It is of the wrong type. It should be a list of rows”

Steve

What is your expression?