Forum Discussion
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 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 |
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
2 Replies
- Greg_Deckler
Community Champion
Anonymous Try:
Measure = VAR __Format = MAX('Table'[Store format) VAR __Table = FILTER(ALL('Table'),[Store format] = __Format) VAR __Result = AVERAGEX(__Table,[Average revenue]) RETURN __ResultHi 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 🙂