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 Anonymous,
I come back to you, because I did a mistake in my explanation of the problem.
You will find below an example :
| Date | A | B | A/B (%) | Avg -6 OK | Avg -6 NOT OK |
| 04/10/2019 | 100 | 200 | 50 | ||
| 05/10/2019 | |||||
| 06/10/2019 | |||||
| 07/10/2019 | 120 | 200 | 60 | ||
| 08/10/2019 | 140 | 200 | 70 | ||
| 09/10/2019 | 160 | 200 | 80 | ||
| 10/10/2019 | 180 | 200 | 90 | ||
| 11/10/2019 | 150 | 200 | 75 | 70,83 | 75 |
| 12/10/2019 |
In fact, the client needs to know the avg of the 6 last records <> 0.
On the 11th of october, if I do -6, I should stop on 6th, but as it's empty, I take the 5th, which is empty, so I need to take the 4th, which is OK.
Is it possible to handle it ? Because in fact, all the date functions work on calendar date, without handling if the line corresponds to the wanted filters.
Hope it's clear for you 🙂
Thanks for your help.
Karim
[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
- Here's a file with the working measure.
https://1drv.ms/u/s!ApyQEauTSLtOgYMnsGk__jeAJkvMEA?e=5gKPk2
Best
DAnonymous thank you very much !
it seems to be the expected result. I'll validate it with the client, and come back to you ASAP.
- Don't forget to give me Kudos 🙂
Best
D