Forum Discussion
Calculation average rate with 0 values
Hi everybody, I need your help. Our customer needs to calculate an average rate of efficiency on 6 days, without lines = 0. Example : Date Notes 05/10/2019 8 06/10/2019 5...
[6LastNon0Entries Avg] = var __non0EntriesCount = 6 var __currentDay = max( Calendar[Date] ) var __daysWithNonZeros = filter( all( 'Calendar'[Date] ), 'Calendar'[Date] <= __currentDay && [Total Hours] > 0 ) var __6DaysWithNonZeros = topn(__non0EntriesCount, __daysWithNonZeros, 'Calendar'[Date], desc ) var __sum = calculate( [Total Hours], __6DaysWithNonZeros ) var __avg = divide( __sum, __non0EntriesCount ) var __6DaysWithNonZerosCount = countrows( __6DaysWithNonZeros ) var __shouldCalc = __6DaysWithNonZerosCount = __non0EntriesCount return if( __shouldCalc, __avg )Best
D
Hi az38
Thanks for your answer.
You'll find below client data examples.
| Date | Temps théorique | Heures disponibles (comprend les effectifs réels) | Gamme théorique/charge (6jours glissant) % |
| 05/10/2019 00:00 | 55.71 | ||
| 06/10/2019 00:00 | 46.73 | ||
| 07/10/2019 00:00 | 98.98653099999994 | 195.999998 | 52.71 |
| 08/10/2019 00:00 | 113.21901100000008 | 195.999998 | 56.06 |
| 09/10/2019 00:00 | 121.79969699999995 | 195.999998 | 58.52 |
| 10/10/2019 00:00 | 86.29423900000013 | 195.999998 | 52.37 |
But Gamme théorique/charge (6jours glissant) % is not OK.
The client needs to calculate on "6 days before"...
Hope the need is clear 🙂
Thanks.
