Calculating a dynamic benchmark with multiple conditions
Hi all, I am struggling with a problem and I hope you can help. My star schema looks like this: fact sales, dim product, dim country, dim store I am trying to achieve the following (mind you I have a composite model in my report with 2 DQ connections and I cannot use PowerQuery) I have a measure 'revenue'. Based on this measure I want to calculate a revenue share % for my products against the total revenue. Like: product A 20% product B 14% product C 0.4% This is achieved easily by a metric like: product revenue share % VAR total_revenue_fixed = calculate([revenue], REMOVEFILTERS(dim_product[product_name]) VAR revenue_product = [revenue] RETURN DIVIDE(revenue_product, total_revenue_fixedl) Now I want to add more complexity. When selecting a store from a report slicer, I want to see the product revenue share % for similar stores (say store group). The problem is that I need an average of my original product revenue share % per store group to show up per store id (mapped to a certain store group). So I need an average measure of an existing measure in a new context, which is of course not possible in PBI. I need to make sure that my store slicer(s) do not affect my original 'product revenue share %' and its components (like total revenue). How can I achieve this? I hope it's clear what I am asking.
