Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |