Reply
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)

Attachment
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
Regular Visitor
Posts: 71
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()

 

I have also attached your pbix with the formula added

Attachment