# Power BI User Group Norway

This is an open group. Sign in and click the "Join Group" button to become a group member and start posting.
Frequent Visitor
Posts: 16
Registered: ‎09-01-2016
##### Get the Average of Below Top 6.

Hi All,

I'm new to DAX I been struggling to solve the issue below. Please see attached and the requirements below. Thanks in advance!!!!

I have a period table which contains a list of date per month

Period Table:

Period

1/1/2013

2/1/2013

3/1/2013

---

12/1/2013

etc...

then i have a fact table which contains the months delay per period.

Fact Table:

Period           MonthsDelay      Project

1/1/2013       23.14                     XProject

1/1/2013       20.14                     YProject

2/1/2013       13.14                     XProject

2/1/2013       40.14                     YProject

3/1/2013       13.14                     XProject

3/1/2013       50.14                     YProject

4/1/2013       13.14                     XProject

4/1/2013       50.14                     YProject

5/1/2013       13.14                     XProject

5/1/2013       50.14                     YProject

6/1/2013       13.14                     XProject

6/1/2013       50.14                     YProject

Now, what I need to do first is get the average of MonthsDelay Per Period (This is Monthly).

After I get the MonthsDelay Per Period. I need to calculate the top 6 bottom moving average on each Period. So the output should be

(this is measure)                  (this is measure and my problem) <--How do i compute this?

Period            AverageDelayPerMonth     Top6MovingAverage

1/1/2013       15                                        0      <-- Dont calculate if lower than 6 period

2/1/2013       16                                        0       <-- Dont calculate if lower than 6 period

3/1/2013       17                                        0       <-- Dont calculate if lower than 6 period

4/1/2013       18                                        0       <-- Dont calculate if lower than 6 period

5/1/2013       19                                        0       <-- Dont calculate if lower than 6 period

6/1/2013       20                                        17.5   <-- from 6/1/2013 to 1/1/2013 (ignore blank or 0 values)

7/1/2013       (Blank or 0)                          17.5   <-- from 7/1/2013 to 1/1/2013 (ignore blank or 0 values)

8/1/2013       22                                        18.7    <-- from 8/1/2013 to 2/1/2013 (ignore blank or 0 values)

9/1/2013       23                                        19.8    <-- from 9/1/2013 to 3/1/2013 (ignore blank or 0 values)

10/1/2013     24                                        21.0    <-- from 10/1/2013 to 4/1/2013 (ignore blank or 0 values)

11/1/2013     25                                        22.1    <-- from 11/1/2013 to 5/1/2013 (ignore blank or 0 values)

12/1/2013     26                                        23.3    <-- from 12/1/2013 to 6/1/2013 (ignore blank or 0 values)

Frequent Visitor
Posts: 16
Registered: ‎09-01-2016
##### Re: Get the Average of Below Top 6.

Can anyone help me or any suggestion on the my requirements above?

Highlighted
Member
Posts: 136
Registered: ‎11-18-2016
##### Re: Get the Average of Below Top 6.

The Following Measure should do the trick,

```Conditional Moving Average =
var rowCount = COUNTROWS(TOPN(6, filter(all('npi_baseline_tracker_period'), 'npi_baseline_tracker_period'[Period] <= max('npi_baseline_tracker_period'[Period])&& [Measure_Monthly_TTM] <> Blank() ))) return
if(rowCount =6,
CALCULATE(AVERAGEX('npi_baseline_tracker_period',[Measure_Monthly_TTM]),TOPN(6, filter(all('npi_baseline_tracker_period'), 'npi_baseline_tracker_period'[Period] <= max('npi_baseline_tracker_period'[Period])&& [Measure_Monthly_TTM] <> Blank() ),[Period] ,DESC)), BLANK())```

it calculates the average of the [Measure_Monthly_TTM] for the top 6 rows of the npi_baseline_tracker_period table where the period is <= the currentperiod and the value of the [Measure_Monthly_TTM] <> Blank() only if the value of the variable rowCont = 6 otherwise the value is blank()