Forum Discussion
Calculation average rate with 0 values
[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
When you calculate an average from a sequence of numbers and want to ignore 0's, you have to just sum up all the numbers (0's don't contribute anything) as they are and find out how many non-0's you have in the sequence, then divide by the count. Quickly, a measure that would give you such an average going 5 days back (as you want to have the last 6 days counting the one you're on right now) would be:
// Calendar must be the Date table in your model.
// Slicing can only be done through dimensions.
// All columns in your fact table must be hidden.
// Base measure
[Total Hours] = SUM( FactTable[Hours] )
[6-Day Avg] =
var __totalDays = 6
var __currentDay = lastdate( Calendar[Date] )
var __firstDay =
dateadd( __currentDay, -(__totalDays - 1), day)
var __periodToAvgOver =
datesbetween(
Calendar[Date],
__firstDay,
__currentDay
)
var __sum =
calculate(
[Total Hours],
__periodToAvgOver
)
var __countOfNonZeroDays =
sumx(
__periodToAvgOver,
1 * ( [Total Hours] > 0 )
)
var __avg =
divide( __sum, __countOfNonZeroDays )
return
__avgTake the above, think it over and adjust to the problem at hand.
Best
D
Hello,
az38 you're right, my result is false. For every record, I need to do Temps Théorique / Heures disponibles. Then I want to make an average of it on last 6 days, without 0 values. So in this example, I should divide by 4 records. Hope one more time I'm clear 🙂
Anonymous Thanks for your answer ! Your code seems to be good, I'll validate it with the client, and come back to you ASAP.
Thank you very much guys !
Karim
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