Forum Discussion

paulsmit's avatar
paulsmit
Frequent Visitor
4 years ago
Solved

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.0...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    Ah, OK.

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

    I think that should do it.

johnt75's avatar
johnt75
Super User
4 years ago

Ah, OK.

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

I think that should do it.

paulsmit's avatar
paulsmit
Frequent Visitor
4 years ago

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's avatar
    johnt75
    Super User
    4 years ago

    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