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 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 TTRRows
9.16666671
4.53333332
3.81666673
2.93333334
2.55
2.46666676
1.97
1.83333338
1.71666679
1.610
0.733333311
0.383333312
0.233333313
Average2.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 HrsRows
8.951
8.68333332
7.76666673
6.06666674
5.98333335
4.456
4.18333337
4.08333338
3.51666679
3.483333310
3.116666711
2.3512
1.613
1.516666714
1.416666715
1.316
1.1517
1.116666718
0.983333319
0.716666720
0.566666721
Average2.914035088

 

 

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

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    7 years ago

    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))
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

      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.

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