Forum Discussion
Minimum and Maximum weekly average by location
Hi,
I'm looking to see if the gap between the highest weekly location average index and the lowest weekly location average index is growing.
| Week | Location | Index |
| 1 | Hartington | 100.00 |
| 1 | Borrowdale | 95.44 |
| 1 | Borrowdale | 83.76 |
| 1 | Hartington | 100.00 |
| 1 | Borrowdale | 96.33 |
| 1 | Borrowdale | 84.99 |
| 1 | Snowdon | 91.73 |
| 1 | Snowdon | 98.27 |
| 1 | Snowdon | 89.30 |
| 1 | Treyarnon | 97.37 |
| 1 | Treyarnon | 100.00 |
| 1 | Treyarnon | 99.16 |
| 2 | London | 100.00 |
| 2 | London | 95.44 |
| 2 | Cambridge | 83.76 |
| 2 | Cambridge | 100.00 |
| 2 | Scotland | 96.33 |
| 2 | Scotland | 84.99 |
| 2 | Scotland | 91.73 |
| 2 | Derby | 98.27 |
| 2 | Derby | 89.30 |
| 2 | Derby | 97.37 |
| 2 | Derby | 100.00 |
| 2 | Mansfield | 99.16 |
From the data above I would expect to get the following results
| Week | Min Index | Max Index | Diff between Max and Min Index |
| 1 | 90.13 | 100.00 | 9.87 |
| 2 | 91.02 | 99.16 | 8.14 |
Cheers
Paul
Ah, OK.
Min Index = MINX(
ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[Location] ), "@avg", CALCULATE( AVERAGE('Table'[Index]) ) ),
[@avg]
)I think that should do it.
5 Replies
Ah, OK.
Min Index = MINX(
ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[Location] ), "@avg", CALCULATE( AVERAGE('Table'[Index]) ) ),
[@avg]
)I think that should do it.
Thank you so much 🙂
If for some reason I needed to take this to the next level and I wanted to get the min and max based on the average of the top 3 and bottom 3 scoring locations (rather than just the top and bottom scoring locations) is that possible?TOPN is the function you'd need to look at. You can specify whether the value you are ranking on should be ascending or descending, so you can get either the top or bottom 3. The actual code for ranking would depend on whether you wanted to take absolute values or averages, but you would then be able to run AVERAGEX over the tables returned by TOPN
Create the below 3 measures
Min Index = MIN( 'Table'[Index] )
Max Index = MAX( 'Table'[Index] )
Diff between Min and Max Index = [Max Index] - [Min Index]Then add all 3 measures to a table, or other visual, with the Week column from your table
Apologies, I probably didn't explain in my initial post that I need to average the index for each location before I find the min and max values
