cancel
Showing results for
Did you mean:
Frequent Visitor

## Combine two values based on text

Hello,

I have a set of data that I'm hoping you all can help me to improve. We have a newsletter that we send out to our employees, anybody who does not open the newsletter is then send another copy of that newsletter. So what we end of having when we extract our data from constant contact is a campaign name like "'Newsletter Name' - August 22, 2017" and "'Newsletter Name' - August 22, 2017 SECOND SEND" some are also formatted "SECOND SEND: 'Newsletter Name': August 22, 2017"

We would like to track how many people click both the first and second send. So what I'm looking to do is try to figure out some way that I can look up off of the text date i.e. "August 22, 2017" and then combine Total Clicks to get 1 overall value. So based on the August 22, 2017 I would hope to get Total Unique Clicks = 435+67 (502). I would also like this to be dynamic so if we add additional values in the future it would perform this same function.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Combine two values based on text

It seems that you want to calculate the Total Unique Clicks based on the same group.

You could create the calculated column or the measure to achieve that.

Here is my simple sample.

Assuming that this is the original table.

Then you could split columns in Query Editor and you will get the table like below.

Then you could create the calculate column or the measure.

```Column =
CALCULATE (
SUM ( Table1[Ticks] ),
FILTER (
'Table1',
'Table1'[Company - Copy.1] = EARLIER ( 'Table1'[Company - Copy.1] )
)
)

Measure = CALCULATE(SUM(Table1[Ticks]),ALLEXCEPT(Table1,'Table1'[Company - Copy.1]))```

Here is the ouput.

More details, you could refer to my attachment.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: Combine two values based on text

It seems that you want to calculate the Total Unique Clicks based on the same group.

You could create the calculated column or the measure to achieve that.

Here is my simple sample.

Assuming that this is the original table.

Then you could split columns in Query Editor and you will get the table like below.

Then you could create the calculate column or the measure.

```Column =
CALCULATE (
SUM ( Table1[Ticks] ),
FILTER (
'Table1',
'Table1'[Company - Copy.1] = EARLIER ( 'Table1'[Company - Copy.1] )
)
)

Measure = CALCULATE(SUM(Table1[Ticks]),ALLEXCEPT(Table1,'Table1'[Company - Copy.1]))```

Here is the ouput.

More details, you could refer to my attachment.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements