N values MIN/MAX EDITOR QUERY

[twister8889]

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 months 02-2020 and 04-2020 (because in this case its the minors values, I need to find 2 months until 06-2020), after that, I calculate the average from this.

Average Max

I find the Values from months 05-2020 and 06-2020 (because in this case its the bigger values,), after that, I calculate the average from this.

If I have more months I need to verify more months, but in this case, I would like to know, how can I get the N (in this case 2 min/max) values from the column values considering one period in this case until 06-2020? (But this period can be changed dynamically )

[Fowmy · Accepted solution]

@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]

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]

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