Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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. 

 

 

4 Replies