Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
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])
))))))))
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
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] ) )
@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))
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |