Forum Discussion
Helper VN 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 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 )
| Month | Value | Average Min | Average Max |
| 01-2020 | 0.244352 | 0.077247 | 0.672917 |
| 02-2020 | 0.105002 | 0.077247 | 0.672917 |
| 03-2020 | 0.211767 | 0.077247 | 0.672917 |
| 04-2020 | 0.049492 | 0.077247 | 0.672917 |
| 05-2020 | 0.27301 | 0.077247 | 0.672917 |
| 06-2020 | 1.072824 | 0.077247 | 0.672917 |
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
7 Replies
- MFelix
Super User
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.
twister8889First of all, thank you for your answer...
I opened the thread in the power query. So I will analyze your suggestion, but I will keep just the question in power query thread. I'm sorry for the inconvenient.
Thank you Miguel
- MFelix
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.
- FrankAT
Community Champion
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
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:
Month Low HIgh 01/2020
0,122 0,530 02/2020 0,122 0,530 03/2020 0,122 0,530 04/2020 0,122 0,530 05/2020 0,122 0,530 06/2020 0,122 0,530 07/2020 0,122 0,530 08/2020 0,090 0,400 09/2020 0,130 0,545 Thank you so much.
- FrankAT
