Help in calculating variance dynamically in Table/Pivot table.

Rajakumar Burra

I have sprint estimates and I want calculate difference between average and each sprint total estimates.

I am getting below in OAC.

Iteration	Estimates	Product Average	Variance
Iteration	241002	Estimates	152	Product Average	107.0	Variance	0.0
Iteration	241016	Estimates	162	Product Average	123.0	Variance	0.0
Iteration	241030	Estimates	173	Product Average	135.0	Variance	0.0
Iteration	241113	Estimates	325	Product Average	134.0	Variance	0.0
Iteration	241120	Estimates	395	Product Average	198.0	Variance	0.0

I want like this.

Iteration	Estimates	Product Average	Variance
Iteration	241002	Estimates	152	Product Average	241.4	Variance	-89.4
Iteration	241016	Estimates	162	Product Average	241.4	Variance	-79.4
Iteration	241030	Estimates	173	Product Average	241.4	Variance	-68.4
Iteration	241113	Estimates	325	Product Average	241.4	Variance	83.6
Iteration	241120	Estimates	395	Product Average	241.4	Variance	153.6

Attached DVA file also for quick reference

Appreciated help.

Variance Test.zip

Dhaval Parikh Stantec

@Rajakumar Burra Somehow, I am unable to upload the DV workbook (its failing on me). But I am attaching the screenshots of final output and individual calculated fields below:

Alex Rubin-Oracle

Hi @Rajakumar Burra, I downloaded the data from your sample into Excel and removed duplicates by Story ID. Then I imported the data back into OAC. Please find attached the solution.

Variance Calc.zip

Rajakumar Burra

Thanks @Alex Rubin-Oracle for quick reply.

Unfortunately I can’t remove the duplicates . Spreadsheet is just example and dataset is SA and it will have multiple duplicate ids.

I need to calculate the average based on users and based on iterations and few other Columns as well.

Alex Rubin-Oracle

https://community.oracle.com/products/oracleanalytics/discussion/comment/65945#Comment_65945

There is no easy formula to solve the average calculation question if there's only one table like in the example. The data model needs to change: 2 datasets - one with distinct story IDs and the other with all stories, connected by the iteration and the stories. That will allow you to group and calculate averages.

Dhaval Parikh Stantec

@Rajakumar Burra

This is how I would approach it. I usually like to break down these tricky calculations:

1) Calculate the Numerator for the Average :
SUM(MIN(Estimates BY Iteration)

2) Calculator the Denominator for the Average:
SUM(COUNT(DISTINCT 1 BY Iteration))

3) Calculate your Product Average by dividing (1) by (2)

4) Calculate your variance by subtracting Product Average you get in step 3 from Estimates

I tried this on a sample dataset and it works for me

Rajakumar Burra

Thanks @Dhaval Parikh Stantec

Please can you share the DVA file if possible. Still I am struggling with your solution.

Rajakumar Burra

Thank you very much @Dhaval Parikh Stantec .

Your solution worked out.

Dhaval Parikh Stantec

Glad it worked out!