0

I want to find difference between two sets of data, where I aggregate the value for row set one and row set two and find the difference between the two..

I am looking for something like this -

enter image description here

Rupa SS
  • 19
  • 3

1 Answers1

0

you can accomplish this via calculated columns and a combination of OVER + Intersect statement. something like the following:

calculated column 1 to get the total 

sum([value]) OVER(intersect([country], [location])) as [country_location_total]

This expression results in the sum of the values for records with matching country and location.

calculated column 2 to get the delta:

[country_location_total]-(2*[value]) as [delta]

here we are taking the sum from calc1 and subtracting 2x the value from the current record to get the delta between category 1 and category 2. This only works if there are exactly two categories. From your example data it looks like you may also be looking for the absolute value of the delta in which case you would wrap all of the expression for col2 in abs()

dean
  • 36
  • 3