Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Schneider879
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
v-piga-msft
Resident Rockstar
Resident Rockstar

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
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.