Moving Average of values excluding 10% of lowest and 70% of highest with filter

[seppel123]

Hi all,

i have a problem in Power BI that I want to solve.

I have a table with data from a production line. The production has steps and each of these steps has a duration time. In my Power BI Dashboard I have all these steps with the corresponding times. These times differ from time to time and I want to calculate a meaningful average time. My plan was to calculate a table where 10% of the lowest duration times are neglected. From the remaining duration times I want to take an average of the lowest 20 %.
Additionally, i want it to be an moving average over the last 3 month. With this i can see differences during different seasons which is of interest. 

My query is called Reactor: 

How can I build that up as an calculated column ?

i searched the forum and found this for a moving average neglecting 5% of top and 5% of bottom:

Measure = 
var _a = COUNTROWS('Table')
return AVERAGEX(TOPN(0.9*_a,TOPN(0.95*_a,'Table',[Value],ASC),[Value],DESC),[Value])

from: https://community.fabric.microsoft.com/t5/Desktop/Exclude-Lowest-and-Highest-Values/td-p/2789580

also i found a moving average over the last 3 month like this: 

1. Create a calculated column for the date.
   

   FullDate =
   DATE ( 2016, 'Session'[Month of the Year], 1 )

2. Create a measure for 3 months moving average. You can change the number of months if you want.
   

   Moving_Average_3_Months = 
   CALCULATE (
       AVERAGEX ( 'Session', 'Session'[Sessions] ),
       DATESINPERIOD (
           'Session'[FullDate],
           LASTDATE ( 'Session'[FullDate] ),
           -3,
           MONTH
       )
   )

From: https://community.fabric.microsoft.com/t5/Desktop/Moving-Average/m-p/43041

But i could not combine it to work for me... 

Thank you and best regards

Sebastian

[Anonymous · Accepted solution]

Hi @seppel123 ,

I checked the expression again and it is fine, the problem is with the number of lines. For example, for January 2021, there are 14 rows, for 10% and 70%, I chose to round down, so it is 1 row of data and 9 rows of data, respectively, and the 20% in the final calculation is actually 2 rows of data, so it results in inaccurate data.

Moving Avg 3Month = 
VAR _RelevantTime = DATESINPERIOD('Reactor'[Date], LASTDATE('Reactor'[Date]), -3, MONTH) 
var _Row = CALCULATE(COUNTROWS('Reactor'), _RelevantTime) 
var _70HP =SUMX(TOPN(0.7*_Row,'Reactor',[Duration],ASC),[Duration])
var _10LP = SUMX(TOPN(0.1*_Row,'Reactor',[Duration],DESC),[Duration])
var _Total = CALCULATE(SUM('Reactor'[Duration]),_RelevantTime)
var _Result =_Total - _70HP -_10LP 
RETURN VALUE(DIVIDE(_Result,0.2*_Row)) 

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @seppel123 ,

Please try:

Moving Avg 3Month = 
VAR _RelevantTime = DATESINPERIOD('Reactor'[Date], LASTDATE('Reactor'[Date]), -3, MONTH) 
var _Row = CALCULATE(COUNTROWS('Reactor'), _RelevantTime) 
var _70HP = SUMX(TOPN(ROUNDDOWN(0.7*_Row,0),'Reactor',[Duration],DESC),[Duration])
var _10LP = SUMX(TOPN(ROUNDDOWN(0.1*_Row,0),'Reactor',[Duration],ASC),[Duration])
var _Total = CALCULATE(SUM('Reactor'[Duration]),ALLEXCEPT('Reactor','Reactor'[Date / Time]) )
var _Result =_Total - _70HP -_10LP 
RETURN VALUE(DIVIDE(_Result,ROUNDDOWN(0.2*_Row,0))) 

I'm not quite sure what you are filtering for, please change it to the correct one at allexcept. The problem now is that the value _Total needs to be calculated for the total value of those rows of _Row.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[seppel123]

Hi Scott,

i tried to show you in an image what i want to achieve: 

In the upper part: based on the 10 entries the determination of the average w/o the lowest 10% and highest 70% results in a best time of 1,015. 

Seen this in the year picture the i want to have a moving average, so i can always relate to a broader time frame to check for the best time and not a snapshot of a month. Eventually i will even do longer timeframes for the moving average to get a complete picture. 

The filtering i did is indeed not right, but the problem is not the _Row, _70HP, _10LP, _Total oder _Result part, the problem is the time filter part and i cant figure out why. 

Maybe this helps to understand my problem and my case better! 

Thanks and best regards

Sebastian

[Anonymous]

Hi @seppel123 ,

There seems to be a problem with the filtering, all the data should still be filtered by _Row, we always get the total number of rows based on the columns of data obtained by _Row, then calculate the sum of 10% and 70%, and then subtract both of them from the total sum to get the final value.

Please try:

Moving Avg 3Month= 
VAR _RelevantTime = DATESINPERIOD('Table'[Date], LASTDATE('Table'[Date]), -3, MONTH) 
var _Row = CALCULATE(COUNTROWS('Table'), _RelevantTime) 
var _70HP = CALCULATE(SUMX(TOPN(ROUNDDOWN(0.7*_Row,0),'Table',[Duration],DESC),[Duration]), _Row) 
var _10LP = CALCULATE(SUMX(TOPN(ROUNDDOWN(0.1*_Row,0),'Table',[Duration],ASC),[Duration]), _Row) 
var _Total = CALCULATE(SUM('Table'[Duration]), _Row) 
var _Result =_Total - _70HP -_10LP 
RETURN VALUE(DIVIDE(_Result,ROUNDDOWN(0.2*_Row,0))) 

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[seppel123]

Hi Scott,

thanks for your reply. Unfortunately that made an error in my measure. I uploaded my pbix file here:

I uploaded the pbix file here: 

https://we.tl/t-EhGP3Lmxfu

As can be seen in the PBIX file the Moving Average over 3 Month does not work. It is a simple average over all time dates available. The RelevantTime Variable seems to not work. The filter for removing lower and upper values is working. 

Thank you so much for your help!

Best regards

Sebastian

[seppel123]

Hi Scott,

thank you very much for the help. 

I tried it out and changed the code a little (ASC <-> DESC) and given the monthly basis it works.

 However, I would like to integrate the 3 month moving average idea, so have a better overview of the developed of the duration times. I tried the following, but it does not work for me - it always gives the same values as without the Time filter:

Moving Avg 3Month= 
VAR _RelevantTime = DATESINPERIOD('Table'[Date], LASTDATE('Table'[Date]), -3, MONTH) 
var _Row = CALCULATE(COUNTROWS('Table'), _RelevantTime) 
var _70HP = CALCULATE(SUMX(TOPN(ROUNDDOWN(0.7*_Row,0),'Table',[Duration],DESC),[Duration]), _RelevantTime) 
var _10LP = CALCULATE(SUMX(TOPN(ROUNDDOWN(0.1*_Row,0),'Table',[Duration],ASC),[Duration]), _RelevantTime) 
var _Total = CALCULATE(SUM('Table'[Duration]), _RelevantTime) 
var _Result =_Total - _70HP -_10LP 
RETURN VALUE(DIVIDE(_Result,ROUNDDOWN(0.2*_Row,0))) 

Any advice? 

Best regards

Sebastian

[Anonymous]

Hi @seppel123 ,

Based on your description, I made simple samples and you can check the results as follows:

You can round up or down the number of rows to suit your needs.

20%Result = var _Row = COUNTROWS('Table')
var _70HP = SUMX(TOPN(ROUNDDOWN(0.7*_Row,0),'Table',[Duration],ASC),[Duration])
var _10LP = SUMX(TOPN(ROUNDDOWN(0.1*_Row,0),'Table',[Duration],DESC),[Duration])
var _Total = SUM('Table'[Duration])
var _Result =_Total - _70HP -_10LP
RETURN VALUE(DIVIDE(_Result,ROUNDDOWN(0.2*_Row,0)))

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.