Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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 fo...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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 🙂