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
  • Fowmy's avatar
    Fowmy
    6 years ago

    twister8889 

    You can create two measures for AverageX measures and a table to select the count:

    Download the file: https://1drv.ms/u/s!AmoScH5srsIYgYNqGMDklTewnvq8Cw?e=YVNfc3

     

     

     

    Avg Max = 

AVERAGEX(
    TOPN( [RnageNo] , 'Table' , 'Table'[Value] ,DESC),
    'Table'[Value]
)

Avg Min = 

AVERAGEX(
    TOPN( [RnageNo] , 'Table' , 'Table'[Value] ,ASC),
    'Table'[Value]
)

RnageNo = SELECTEDVALUE(Range[Value],2)

     



     

    Range = 
GENERATESERIES( 1 , 
    CALCULATE(
        COUNTROWS('Table'),ALL('Table' )
    ),1
)

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon on the right if you like this reply 🙂

    YouTube, LinkedIn

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

twister8889 

You can create two measures for AverageX measures and a table to select the count:

Download the file: https://1drv.ms/u/s!AmoScH5srsIYgYNqGMDklTewnvq8Cw?e=YVNfc3

 

 

 

Avg Max = 

AVERAGEX(
    TOPN( [RnageNo] , 'Table' , 'Table'[Value] ,DESC),
    'Table'[Value]
)

Avg Min = 

AVERAGEX(
    TOPN( [RnageNo] , 'Table' , 'Table'[Value] ,ASC),
    'Table'[Value]
)

RnageNo = SELECTEDVALUE(Range[Value],2)

 



 

Range = 
GENERATESERIES( 1 , 
    CALCULATE(
        COUNTROWS('Table'),ALL('Table' )
    ),1
)

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

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

    First of all, thank you for your answer

     

    I'm trying to understanding the solution, but I need to repeat the values AVG for the all months minors (< 😃 that June (red line) because the average is to six months, but when the month be July The AVG is by 7 months, Aug by 8 months.  Another doubt is, until the specific month (12), all previous months need to have the same value that the max month, in this case, all months before and equal June, need to have value 0.342.

    My presentation will be in the line chart, Month x Final value.

     

    To June the expected value is 0.342 and not 0.575

     

    The link

     

     https://1drv.ms/u/s!AoDYwrtLrltJnxchsjiioGINCFvt

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Is not very clear the output expected

      But if this is near your needs, you can change the number 2 inside this expression

      List.Average( List.MinN(ct[value],2))

      to exetnd the average to n values/months.

       

       

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pco7CsAgDADQu2ROITFK9Czi0IKVDv0gdOnpC7a6Pl6MUPIxGQIEQmOdOEgYYc1LRyYjwk33uT7nyCzBN56v2pFs8PrfMqYGbxuW7f6QkVRdgJRe", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [month = _t, value = _t]),
    ct= Table.TransformColumnTypes(Source,{{"month", type date}, {"value", type number}},"it-IT"),
    ac = Table.AddColumn(ct, "avgMin", each List.Average( List.MinN(ct[value],2))),
    ac1 = Table.AddColumn(ac, "avgMax", each List.Average( List.MaxN(ct[value],2)))
in
    ac1