Forum Discussion
Average based on criteria on same column
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 co...
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
Zubair_Muhammad
Community Champion
Community ChampionAnonymous
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] )
)