Comparing a value to the values total based on a matching dimension.

[Anonymous]

Hi everyone,

In my example, I have 4 unique stores with different store types (small, medium and large format). 

To see if a store performs better or worse than the average of similar store formats, I wants to add a column (marked red in example below) that keeps the average of all the stores with similar format.  So in my example below, the average of large store formats is 100 (=(50+150)/2).

When I have slicers in place, I somehow cant figure out how I can fix this. Can anyone help me? Thanks so much in advance!

Example:

Store ID	Store format	Average revenue	Average of store classification
1	Small	10	10
2	Medium	20	25
3	Large	50	100
4	Large	150	100
5	Medium	30	25

[Greg_Deckler · Accepted solution]

@Anonymous Try:

Measure = 
  VAR __Format = MAX('Table'[Store format)
  VAR __Table = FILTER(ALL('Table'),[Store format] = __Format)
  VAR __Result = AVERAGEX(__Table,[Average revenue])
RETURN
  __Result

[Anonymous]

Hi @Greg_Deckler ,

[edit] It worked! Thanks for the quick reply and solution. Really helpful! 😄
Thanks for your reply! I have used your answer, but it seems it returns the average revenue per row, and not the average of multiple rows in that new column. So the revenue value stays the same. 

Im not quite following the steps in the measure example. What does the MAX('Table'[Store format]) do in this example?

Again, thanks for taking the time, I really appreciate it 🙂