
1. Re: Average over period of daily averages
Michel Caissie Aug 15, 2016 10:47 AM (in response to Anna Yureva)Anna,
You can get Avg you are looking for using only LOD, but you have to do a little gymnastic of inclusion and exclusion to take into account the missing level of detail in the view, and the extra Hours level of detail that is
changing the expected 5.5 or those 5 and 7.
if you look on sheet 8, your LOD calc gives the correct values for each StartDt.
Now if you go at sheet 9, Start Dt have been removed from the view and to obtain the 5.5 avg you need to include the Start Dt in the calc.
avg({include [Start Dt]:
avg({fixed [Start Dt]: avg([Elapsedrunsecs])})
})
If we continue on sheet 10 , we now add the Hours for X axis lod and we get the values that you are stuck with.
Moving to sheet 11 we get back the correct values by excluding the Hours for X axis in the calc
{exclude [Hours for X axis]:
avg({include [Start Dt]:
avg({fixed [Start Dt]: avg([Elapsedrunsecs])})
})
}
which is the final calc that you need in the final Gant Chart(Sheet 7).
Michel

AVG of AVG ed mc.twbx 48.7 KB


2. Re: Average over period of daily averages
Anna Yureva Aug 15, 2016 2:08 PM (in response to Michel Caissie)Michel,
This worked! Thanks for explaining it step by step, it certainly makes a lot of sense!
What about when there are multiple jobs ? When adding more jobs with similar data and filtering to only see results of one, the AVG result is correct (same 5.5) if the filter is added to context. However, when there is no need to filter out a single job, the values should be calculated only per job, and not for all values of that day. I will try to do a table calc on Jobname and see how it works.
Do you think this is a correct approach here?
Attaching a twbx with couple more jobnames.
Regards,
Anna

3. Re: Average over period of daily averages
Michel Caissie Aug 15, 2016 2:20 PM (in response to Anna Yureva)1 of 1 people found this helpfulAll you need is to add jobname in the fixed part of the calculation
{exclude [Hours for X axis]:
avg({include [Start Dt]:
avg({fixed [Jobname],[Start Dt]: avg([Elapsedrunsecs])})
})
}

4. Re: Average over period of daily averages
Anna Yureva Aug 16, 2016 1:31 AM (in response to Michel Caissie)This is perfect, Michel, thanks for your tremendous help!