Calculated Field cross multiple columns with variable column names

Question & Answer
, ,
1

I have a table with 1 to n columns that each have values
The column names are “dynamic”
I want to create a calculated field where I define that only the columns that start with “Revenue” should be added up
See below
Depending on the time frame the user selected, I get different years so I can’t specify the actual column names in the calculated field

I would need something like

sum if column_names starts with “Profit”
sum if column_names starts with “Revenue”

image
image1227×123 5.68 KB

Ideally this would be in a flat table and not a Pivot table but if there is a way to do this in Pivot, I would also consider this

thanks

2

Hi @mlinsin,
Have you tested out a calculated field for this yet, if so, could you share what you have so far?

Ideally for your scenario, you’re looking to add up the columns/fields that contain the same start? So for example, you’d like to sum ‘Revenue_2022’, ‘Revenue_2023’, ‘Revenue_2021’, etc.?

3

I’ve not created a calculated field yet.
But what I would like to do is add up the columns that start with “Revenue”, so regardless of the year.
Like Revenue*

4

Hi @mlinsin,
Unfortunately, there’s not really a way to to add up fields by ‘like’ text. To sum these, you’ll have to create a calc. field that recognizes each specific field that you’d like to combine.

5

Hi @mlinsin,
It’s been awhile since we last heard from you; following up to see if you had any additional questions regarding your initial topic?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!