6 Replies Latest reply on Feb 17, 2020 7:45 AM by jbdc

# LOD Calculations (Find first value, then calculate an average rate of progress)

I have a data set that measures program success across countries. I am using a date filter with a bar chart that allows the user to view average progress over time.

It measures Metric Values against Target Values (in red) over time.

Here is a look at my data  What I'm doing is first using a Lookup calculation to identify the first metric value in one of these sets - LOOKUP (SUM ([Metric Value]), FIRST()) The calculation seems to work. I am calling it "Baseline."

I am then using another calculation to try and measure an average level of progress towards the Target value (Current Metric Value - Baseline/Target Value -Baseline) However, the results don't produce what they should.

Below is the view of the two data (Metric) sections I first displayed (the ones with 0.4 and 0.5 values). They are being measured from a 2012-2015 data filter (but as all the Metric Values for this section don't improve, that shouldn't matter, as the results should be 0%)

Given my calculated fields, the measurements should be displaying - (0.4 - 0.4/0.8 - 0.4) & (0.5 - 0.5/1.6 - 0.5) - which should, together, equal 0% - yet, they indicate (below) a progression rate of .0625 (6.25%)

What am I doing wrong, and how can I make this calculation work?

(Current Metric Value - Baseline/Target Value -Baseline)

Message was edited by: James Bieszka

• ###### 1. Re: LOD Calculations (Find first value, then calculate an average rate of progress)

you forgot a workbook. A simple explanation and a workbook is what is needed to get help. Like, this is what  data is, I did this, I am here, getting no where , need this, help, here is workbook.

• ###### 2. Re: LOD Calculations (Find first value, then calculate an average rate of progress)

Unfortunately I cannot share my data or workbook - if I could, I certainly would.

Is there any part of this where you don't understand?

I'm happy to explain more and/or give more screenshots!

Thanks so much

• ###### 3. Re: LOD Calculations (Find first value, then calculate an average rate of progress)

Sure, I would have, but may be someone can try without workbook.

• ###### 4. Re: LOD Calculations (Find first value, then calculate an average rate of progress)

Lets try this, Attach some fake data and let me know what do you need and what are the expected results

• ###### 5. Re: LOD Calculations (Find first value, then calculate an average rate of progress)

Ok, I will attach some "fake data" I can give, I hope it helps!

And do let me know if you have any further questions I appreciate it so much!

• ###### 6. Re: LOD Calculations (Find first value, then calculate an average rate of progress)

Hello,

Are you able to see the mock excel data I attached?