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 241002 152 107.0 0.0 241016 162 123.0 0.0 241030 173 135.0 0.0 241113 325 134.0 0.0 241120 395 198.0 0.0 I want like this. Iteration Estimates Product Average Variance 241002 152 241.4 -89.4 241016 162 241.4 -79.4 241030 173 241.4 -68.4 241113 325 241.4 83.6 241120 395 241.4 153.6 Attached DVA file also for quick reference Appreciated help. Variance Test.zip

Oracle Communities

Oracle Analytics and AI

Child Item

Help in calculating variance dynamically in Table/Pivot table.

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
241002	152	107.0
241016	162	123.0
241030	173	135.0
241113	325	134.0
241120	395	198.0

I want like this.

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

Attached DVA file also for quick reference

Appreciated help.

Variance Test.zip

Find more posts tagged with

Accepted answers

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:

All comments

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.

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:

Rajakumar Burra

Thank you very much @Dhaval Parikh Stantec .

Your solution worked out.

Dhaval Parikh Stantec

Glad it worked out!