Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
50 | |
19 | |
12 | |
11 |