Forum Discussion
Category Averages and Relative Tag
Hi all,
Could anyone help with the recommended formula for these measures? Am looking to include either in a card or table visualisation;
| Account Name | Country | Grand Total | Grand Total Average Per Country | Below/Above Average Per Country |
| Bob | United States | 23000 | Average of all those where Country = United States | "Below Country Average" |
| Wendy | United States | 4457865 | Average of all those where Country = United States | "Above Country Average" |
| Mary | Canada | 4468477 | Average of all those where Country = Canada | "Above Country Average" |
| Sinclair | United Kingdom | 280492 | Average of all those where Country = United Kingdom | "Country Average" |
| Oberon | Brazil | 22336 | ||
| Harry | United States | 746736 | Etc | Etc |
| Shelly | United States | 27915 | ||
| Dionne | Canada | 38400 | ||
| Brett | United States | 12000 | ||
| Kobe | United Kingdom | 27901 | ||
| Michael | Canada | 70000 | ||
| Grace | United States | 3296237 | ||
| Kelly | Brazil | 71662 |
Thanks so much in advance!
Hi Anonymous ,
You could refer to the following measures:
Grand Total Average Per Country = CALCULATE ( AVERAGE ( 'Table'[Grand Total ] ), ALLEXCEPT ( 'Table', 'Table'[Country ] ) )Below/Above Average Per Country = IF ( SELECTEDVALUE ( 'Table'[Grand Total ] ) < [Grand Total Average Per Country], "below", "above" )Here is my test result:
2 Replies
- v-eachen-msft
Community Support
Hi Anonymous ,
You could refer to the following measures:
Grand Total Average Per Country = CALCULATE ( AVERAGE ( 'Table'[Grand Total ] ), ALLEXCEPT ( 'Table', 'Table'[Country ] ) )Below/Above Average Per Country = IF ( SELECTEDVALUE ( 'Table'[Grand Total ] ) < [Grand Total Average Per Country], "below", "above" )Here is my test result: