Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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.
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
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |