cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Schneider879 Frequent Visitor
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. 

 

Please see the attached picture for reference. Thanks in advance!Sample data.png

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Combine two values based on text

Hi @Schneider879,

 

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.

 

original table.PNG 

 

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

 

after change.PNG

 

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.

output_.PNG

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.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Combine two values based on text

Hi @Schneider879,

 

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.

 

original table.PNG 

 

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

 

after change.PNG

 

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.

output_.PNG

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.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)