Average based on criteria on same column

[Anonymous]

Hi Team,

I have a data as shown below . Please help me designing a DAX code related to the requirement.

In Data shown below, First column contains data in hours and second column is just a count of rows we have in data sample.

Now , I would like to calculate average based on condition. If I have rows less than 15( rows <15)  , then I will have to omit first largest record from Critical TTR column which is 9.166667 in this case. Then I need to calculate average based on remaining rows . So in this case, average for last 12 rows is 2.054167.

Critical TTR	Rows
9.1666667	1
4.5333333	2
3.8166667	3
2.9333333	4
2.5	5
2.4666667	6
1.9	7
1.8333333	8
1.7166667	9
1.6	10
0.7333333	11
0.3833333	12
0.2333333	13
Average	2.0541667

Lets consider a second scenario . I have 21 records(rows  >15 and rows < 30) and  in second sample dataset as shown below.

In this case, i have to omit first 2 records from top and then calculate average for remaining rows.

Critical Hrs	Rows
8.95	1
8.6833333	2
7.7666667	3
6.0666667	4
5.9833333	5
4.45	6
4.1833333	7
4.0833333	8
3.5166667	9
3.4833333	10
3.1166667	11
2.35	12
1.6	13
1.5166667	14
1.4166667	15
1.3	16
1.15	17
1.1166667	18
0.9833333	19
0.7166667	20
0.5666667	21
Average	2.914035088

[v-yuta-msft · Accepted solution]

@Anonymous

You may also try measure below:

Result1 = 
VAR Row_Number = COUNTROWS(Table1)
VAR Condition1 = TOPN(Row_Number - 1, Table1, Table1[Critical TTR], ASC)
VAR Condition2 = TOPN(Row_Number - 2, Table1, Table1[Critical TTR], ASC)
RETURN
IF(Row_Number < 15, AVERAGEX(Condition1, [Critical TTR]), IF(Row_Number > 15 && Row_Number < 30, AVERAGEX(Condition2, [Critical TTR])))

Result2 = 
VAR Row_Number = COUNTROWS(Table2)
VAR Condition1 = TOPN(Row_Number - 1, Table2, Table2[Critical Hrs], ASC)
VAR Condition2 = TOPN(Row_Number - 2, Table2, Table2[Critical Hrs], ASC)
RETURN
IF(Row_Number < 15, AVERAGEX(Condition1, [Critical Hrs]), IF(Row_Number > 15 && Row_Number < 30, AVERAGEX(Condition2, [Critical Hrs])))

 

Regards,

Jimmy Tao

[Anonymous]

Hi,

I do not get the expected results based on all the coditions provided here.Please refer the screenshot, in the conditions i have total 6 counts now when caluclating the new average i have to omit 9.11 when calucalting the average and then divide  remaining sum by 5. Also if the count is just 1 then i don't need to omit anything from the calculation.

Bewlow is the Measure i Created for this.

MTTR =
VAR Row_Number = COUNTROWS('Major/Store Down')
VAR Condition1 = TOPN(Row_Number - 1, 'Major/Store Down', 'Major/Store Down'[TTR], ASC)
VAR Condition2 = TOPN(Row_Number - 2, 'Major/Store Down', 'Major/Store Down'[TTR], ASC)
VAR Condition3 = TOPN(Row_Number - 3, 'Major/Store Down', 'Major/Store Down'[TiTTR], ASC)
VAR Condition4 = TOPN(Row_Number - 4, 'Major/Store Down', 'Major/Store Down'[TTR], ASC)
VAR Condition5 = TOPN(Row_Number - 5, 'Major/Store Down', 'Major/Store Down'[TTR], ASC)
VAR Condition6 = TOPN(Row_Number - 6, 'Major/Store Down', 'Major/Store Down'[TTR], ASC)
VAR Condition7 = TOPN(Row_Number - 7, 'Major/Store Down', 'Major/Store Down'[TTR], ASC)
RETURN
IF(Row_Number =1,  Average('Major/Store Down'[TTR]),
IF(Row_Number >=2 && Row_Number <=14, AVERAGEX(Condition1, 'Major/Store Down'[TTR]),
IF(Row_Number >=15 && Row_Number <=24, AVERAGEX(Condition2, 'Major/Store Down'[TTR]),
IF(Row_Number >=25 && Row_Number <=34, AVERAGEX(Condition3, 'Major/Store Down'[TTR]),
IF(Row_Number >=35 && Row_Number <=44, AVERAGEX(Condition4, 'Major/Store Down'[TTR]),
IF(Row_Number >=45 && Row_Number <=54, AVERAGEX(Condition5, 'Major/Store Down'[TTR]),
IF(Row_Number >=55 && Row_Number <=64, AVERAGEX(Condition6, 'Major/Store Down'[TTR]),
IF(Row_Number >=65 && Row_Number <=74, AVERAGEX(Condition7, 'Major/Store Down'[TTR])
))))))))

[Anonymous]

I too have a smilar requirement for Average calculation, but in my case i need to consider the month as well, i need the average to be caluculated in similar fashion for each month, with these Monthly average we will be creating a trend chart to show the Average.

[Zubair_Muhammad]

@Anonymous

May be  a MEASURE like

Measure =
VAR myvalues =
    FILTER (
        ADDCOLUMNS ( Table1, "Rank", RANKX ( Table1, [Critical TTR],, DESC, DENSE ) ),
        [Rank] > 1
    )
RETURN
    IF (
        COUNTROWS ( Table1 ) < 15,
        AVERAGEX ( myvalues, [Critical TTR] ),
        AVERAGE ( Table1[Critical TTR] )
    )

[ryan_mayu]

@Anonymous

please try below coding for two senarios.

Measure = 
VAR maxamount=MAX(Sheet5[Critical TTR])
return  CALCULATE(AVERAGE(Sheet5[Critical TTR]),FILTER(Sheet5,Sheet5[Critical TTR]<maxamount))

Measure = 
    VAR maxamount=MAX(Sheet5[Critical TTR])
    VAR secondmaxamount=MAXX(FILTER(Sheet5,Sheet5[Critical TTR]<maxamount),Sheet5[Critical TTR])
    return  CALCULATE(AVERAGE(Sheet5[Critical TTR]),FILTER(Sheet5,Sheet5[Critical TTR]<secondmaxamount))