Hello,
I'm trying to create measures for various benchmarks in a marketing dashboard, specifically CTR, View-thru rate, and video completion rate. I want these measures to respond to slicers set at the report level, along with the specific level in hierarchy within a matrix table. The drill down levels are campaign name > creative name > ad size > ad platform, and the page level slicers are for Brand, Date, Campaign, Product, and Platform. I've been playing around with ALLSELECTED() and INISCOPE() functions to try to get the measures to respond to filters and hierarchy levels in the way that I want, but can't get them to work in the way that I want. As an example, I wrote the following formula for a dynamic CTR measure, but the results are the same as the regular CTR measure, which is simply a divide function.
Formula:
Hierarchy CTR =
VAR drillclicks = CALCULATE(SUM(vw_media_summary[Clicks]))
VAR drillimp = CALCULATE(SUM(vw_media_summary[Impressions]))
RETURN
SWITCH(
TRUE(),
ISINSCOPE(vw_media_summary[platform]), DIVIDE(drillclicks, drillimp),
ISINSCOPE(vw_media_summary[ad_size]), DIVIDE(drillclicks, drillimp),
ISINSCOPE(vw_media_summary[creative_name]), DIVIDE(drillclicks, drillimp),
ISINSCOPE(vw_media_summary[creative_campaign]), DIVIDE(drillclicks, drillimp),
[Dynamic CTR%]
)
When I drill down to the lowest level of the hierarchy (platform), the Hierarchy CTR returns the same value as the standard CTR measure we've already setup. The dataset is proprietary, so I can't share it, but it is very extensive and robust, so I should be able to manipulate it in any way that makes sense. Appreciate any help/insight!
