Hi Team,
I have a requirement to find out the average % value of completed vs committed story points %. I am posting a jpg below to show the formulae in excel. Please let me know how this can be implemented in EazyBI.
I want the values to come up as per column E and the formula is given in column F.
Need to know how this can be implemented through EazyBI.
eazyBI allows working with visible rows since version 5.0. You can access all visible rows with function VisibleRowsSet(). We have several default calculations built using those new functions you can use them with an option Add calculated.
The function you are looking for is a Running average. It is similar to the Cumulative sum we support by default. I would suggest using a new option and add a cumulative sum to your report for your measure. Then you can access a formula of cumulative sum with Edit calculated and copy the formula. Paste the formula to the new calculated measure and change SUM to AVG to get your running average.
Here is an example based on a measure Story Points resolved:
CASE
WHEN CurrentTuple(VisibleRowsSet()).Item(0).Name = '$total_aggregate' THEN
[Measures].[Story Points created].Value
WHEN NOT IsEmpty([Measures].[Story Points created]) THEN
AVG(
Head(VisibleRowsSet(), Rank(CurrentTuple(VisibleRowsSet()), VisibleRowsSet())),
[Measures].[Story Points resolved]
)
END
Daina / support@eazybi.com
