8 Replies Latest reply on Oct 27, 2017 2:24 PM by Diogo Braga

# Trying to calculate difference between 2 measures, not table calc

I posted this questions...

...about a month ago. Joe Oppelt gave me the correct answer using table calculations. In the current viz, table calculation will not work.

I want to show by how many percentage points the 2016-2017 spring results increased or decreased compared to 2015-2016 spring. I want to show this result inside the red rectangle for each bar.

Refer to sheet 34

Thank you!

• ###### 1. Re: Trying to calculate difference between 2 measures, not table calc

One step at a time.

First, to capture the 16-17/spring data, make a calc like this:

{ FIXED [Metric] : SUM( IF [School Year] = "16-17" and [Season] = "Spring" then [Value] END)  }

Let's start with that.  I don't know what exactly you want to compare, but whatever it is, do it in a calc like that.

Two calcs actually.  One for 16-17, and one for 15-16.  You'll have those two values on every row, set for each [Metric].  From there it should just be simple math to get the numbers you need.

Once you get that, post the workbook and we can play with how to shove that where you want it on the bar.

• ###### 2. Re: Trying to calculate difference between 2 measures, not table calc

Hey Joe Oppelt , I applied your recommendation, but it's returning all 4s for 15-16 Spring, and all 5s for 16-17 Spring.

Any ideas?

• ###### 3. Re: Trying to calculate difference between 2 measures, not table calc

Some info about FIXED LODs:

FIXED performs the operation before filters are applied.  UNLESS the filter is marked "apply to context".  Here's how you do that:

The FIXED LOD I gave you is taking all rows for each [Metric] and adding up [Value] for any row where [year] = 16-17 and [season] = "spring".

In your data every row has a value of 1 for [value].  In 15-16/spring you have 4 rows total for each [measure], and you have 5 rows total in 16-17.  So that's why you are getting 4 and 5 respectively.  If you put the [School Code] in context, you will change those values to 1.

I just chose [value] to pick a measure for demonstration purposes.  What are you actually looking to compare from year to year?  That's the measure you want to be using in those two FIXED calcs.

• ###### 4. Re: Trying to calculate difference between 2 measures, not table calc

Thanks for the information.

I want to show by how many percentage points the 16-17 spring results increased or decreased compared to 15-16 per METRIC. You helped me with this before, I posted the link to the question here and the screenshot of the solution below. However, a table calculation doesn't work on what I am trying to achieve on the viz, so we are trying with LOD now. The LOD calc should return a unique value for each METRIC, but currently it's returning 4s and 5s.

• ###### 5. Re: Trying to calculate difference between 2 measures, not table calc

Well I explained why you're getting all 4s and 5s, (and if you change the filter to CONTEXT you'll get all 1s.)  That's because the value for [Metric] is 1 for every row.

If you had actual values in there, you'd get different results.

• ###### 6. Re: Trying to calculate difference between 2 measures, not table calc

Where do you see 1 for every row? I thought we were talking about the Value column, and for me it has actual decimal values there...

• ###### 7. Re: Trying to calculate difference between 2 measures, not table calc

Look at Sheet 41 in the attached.

1 of 1 people found this helpful
• ###### 8. Re: Trying to calculate difference between 2 measures, not table calc

Wow...you just helped me finally figure out LOD.

We were missing  'Response = "Strongly Agree" ' in the calculation because all the responses combined equals 100%.

Very grateful for your time! Thank you!