
1. Re: Trying to calculate difference between 2 measures, not table calc
Joe Oppelt Oct 26, 2017 3:22 PM (in response to Diogo Braga)One step at a time.
First, to capture the 1617/spring data, make a calc like this:
{ FIXED [Metric] : SUM( IF [School Year] = "1617" 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 1617, and one for 1516. 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
Diogo Braga Oct 27, 2017 7:34 AM (in response to Joe Oppelt)Hey Joe Oppelt , I applied your recommendation, but it's returning all 4s for 1516 Spring, and all 5s for 1617 Spring.
Any ideas?

SURVEY TB.twbx 945.2 KB


3. Re: Trying to calculate difference between 2 measures, not table calc
Joe Oppelt Oct 27, 2017 8:29 AM (in response to Diogo Braga)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] = 1617 and [season] = "spring".
In your data every row has a value of 1 for [value]. In 1516/spring you have 4 rows total for each [measure], and you have 5 rows total in 1617. 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
Diogo Braga Oct 27, 2017 10:32 AM (in response to Joe Oppelt)Thanks for the information.
I want to show by how many percentage points the 1617 spring results increased or decreased compared to 1516 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
Joe Oppelt Oct 27, 2017 11:02 AM (in response to Diogo Braga)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
Diogo Braga Oct 27, 2017 11:06 AM (in response to Joe Oppelt) 
7. Re: Trying to calculate difference between 2 measures, not table calc
Joe Oppelt Oct 27, 2017 1:38 PM (in response to Diogo Braga)Look at Sheet 41 in the attached.

SURVEY TB_v10.4.twbx 945.2 KB


8. Re: Trying to calculate difference between 2 measures, not table calc
Diogo Braga Oct 27, 2017 2:24 PM (in response to Joe Oppelt)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!