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!
Assuming the EnumList has a base type of Ref, you might be able to do this:
SUM([EnumListOfRef][Numeric])
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.
In what way doesn’t it work?
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…
Thank you, this helped me a lot
@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.
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”
What is your expression?
User | Count |
---|---|
41 | |
31 | |
31 | |
16 | |
14 |