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

     

FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
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

 

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

    thank you for your answer @FrankAT

     

    I have one scenario complement: My period to find the average numbers (lowest, highest) is 7 months. So in my next example, when month 8 be load, I need to find my average (lowest, highest) to the months 2,3,4,5,6,7,8. When month 9 be load I need to find my average to the months 3,4,5,6,7,8,9. So my range of data is changing with the increase of months. After that of month 7, the average numbers (lowest, highest) can't repeat, only until July I can repeat the values to the previous month (like your print), how to improve to consider that the month 8 and months bigger, the data (lowest, highest) will not be repeated? The final table is like this:

     

    MonthLowHIgh

    01/2020

    		0,1220,530
    02/20200,1220,530
    03/20200,1220,530
    04/20200,1220,530
    05/20200,1220,530
    06/20200,1220,530
    07/20200,1220,530
    08/20200,0900,400
    09/20200,1300,545

     

    Thank you so much.

    • FrankAT's avatar
      FrankAT
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi twister8889 

      why shouldn't the data of month 8 and months bigger be repeated?

      Regards FrankAT

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

        Hello, I updated the question now above, sorry.
        Because my business rule is: data before month 8, needed to assume the same values that minimal range months (7). And my minimal period to calculate the average is 7 months.