Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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