Forum Discussion

twister8889's avatar
twister8889
Icon for Helper V rankHelper V
6 years ago
Solved

N values MIN/MAX EDITOR QUERY

Hi guys,   I have some data like the table below, I need to find the N min/max values per period to calculate the average, for example, in the first row:   Average Min: I find the Values from mo...
Need Help
  • FrankAT's avatar
    FrankAT
    6 years ago

    Hi twister8889 

    here is my solution. Use the following measures:

     

    Sum of Value = SUM('Table'[Value])

Highest = 
CALCULATE (
    AVERAGEX (
        TOPN ( 'TopN'[TopN Value], 'Table', [Sum of Value], DESC ),
        'Table'[Value]
    ),
    ALL ( 'Table' )
)

Lowest = 
CALCULATE (
    AVERAGEX (
        TOPN ( 'TopN'[TopN Value], 'Table', [Sum of Value], ASC ),
        'Table'[Value]
    ),
    ALL ( 'Table' )
)

     

    You can dowmload the pbix file here.

    Regards FrankAT

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Hi twister8889 ,

 

Create the following 3 measures:

 

Value_Sum = SUM('Table'[Value])

Lowest = 
AVERAGEX (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Month],
            'Table'[Value],
            "@Ranking", RANKX ( ALLSELECTED ( 'Table' ), [value_Sum],, ASC, SKIP )
        ),
        [@Ranking] <= 2
    ),
    [value_Sum]
)


HIGHEST = 
AVERAGEX (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Month],
            'Table'[Value],
            "@Ranking", RANKX ( ALLSELECTED ( 'Table' ), [value_Sum],, DESC, SKIP )
        ),
        [@Ranking] <= 2
    ),
    [value_Sum]
)

 

If you want the [@Ranking]<= 2 can also be dinamic wiht an WHAT IF analysis.

 

Check PBIX file attach.

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

      Hi @twister8889,

      You need this in Power Query?

      Be aware that in Power Query the dinamic part is not as linear as in DAX, because when you select the parameters for your query to calculate after the upload they stay as you defined them then you need to reselect and refresh the query.