Minimum and Maximum weekly average by location

[paulsmit]

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

[johnt75 · Accepted solution]

Ah, OK.

Min Index = MINX(
    ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[Location] ), "@avg", CALCULATE( AVERAGE('Table'[Index]) ) ),
    [@avg]
)

I think that should do it.

[paulsmit]

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?

[johnt75]

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

[johnt75]

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

[paulsmit]

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