Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a dataset that looks something like this:
Campaign | Mailing List | Date | Open Rate |
A | Subscribers | 01/05/2016 | 15% |
B | Competition | 03/09/2016 | 17% |
C | Competition | 25/11/2016 | 23% |
D | Subscribers | 08/12/2016 | 14% |
E | Competition | 04/01/2017 | 19% |
F | Subscribers | 15/01/2017 | 29% |
What I'm trying to achieve (and failed so far) is to dynamically compare the Open Rate of a specific campaign that is chosen via a slicer in the report, against campaigns with similar characteristics, for example:
- Similar mailing list (e.g. if I choose Campaign F in the filter, I want to compare its Open Rate against the average Open Rate of all campaigns that went to Subscribers)
- YTD (e.g. if Campaign F is selected, compare its Open Rate against all campaigns that ran in 2017)
Any help is much appreciated!
Many thanks,
George.
Solved! Go to Solution.
@Anonymous
Hi George,
I've used your "Similar Mailing List" example, but similar logic should apply for your other example.
Before getting into the DAX, you need a measure that:
If I call your table Data, this sort of pattern would work:
Open Rate Average = AVERAGE ( Data[Open Rate] ) Open Rate Average for Same Mailing List as Selected Campaigns = CALCULATE ( [Open Rate Average], SUMMARIZE ( Data, Data[Mailing List] ), ALL ( Data[Campaign] )
)
Notes:
Hope that helps as a starting point.
Cheers,
Owen 🙂
@Anonymous
Hi George,
I've used your "Similar Mailing List" example, but similar logic should apply for your other example.
Before getting into the DAX, you need a measure that:
If I call your table Data, this sort of pattern would work:
Open Rate Average = AVERAGE ( Data[Open Rate] ) Open Rate Average for Same Mailing List as Selected Campaigns = CALCULATE ( [Open Rate Average], SUMMARIZE ( Data, Data[Mailing List] ), ALL ( Data[Campaign] )
)
Notes:
Hope that helps as a starting point.
Cheers,
Owen 🙂
It worked a treat!! Many thanks Owen!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |