Jump to content

Complex Calculation

Stuart Wise

Recommended Posts

Hi all,

This is a bit of a strange one.

I have a list of Locations that I use as my Columns in a Cross table and the Volumes is a sum of the Volume from each location.

But for one particular column I need it to subract the volume of another column from it.

Below is what I am trying so far, so it's a case statement inside a case statement

Sum([Volume]) then


when [Columns.LOCATION]="LocationX" then [Value] -


when [Columns.LOCATION]="LocationY" then [Value]


else [Value]

end as [Volume]


There are actually about 10 Locations, but I changed it on here for GDPR reasons

But basically what I need is the Sum of the volume unless it is location X in which case I want LocationX Volume - LocationY Volume.

I hope that makes sense as it's been driving me mad for a while now



As always, any help fully appreciated.


Kind Regards,


Link to comment
Share on other sites

Bit of an Update.


I think the reason this doesn't work is because I have already filtered to LocationX in the first Case Statement so it can't find LocationY.


So I think I understand the issue, just no clue how to solve it.


Kind Regards,

Link to comment
Share on other sites

You can have multiple expressions in a case() statement. For example:


case when [Location]=X then "xresult" when [Location]=Y then "yresult" when [Location]=Z then "zresult" else "abcresult" end


Or you could turn the case() statement around if Location Y is the only aberration. Something like:


case when [Location]=Y then "yresult" else "defaultresult" end

Link to comment
Share on other sites

  • 2 weeks later...



Thanks for the reply.


The issue is that when it is [Location]=X I want to to be the value minus the value when it's [Location]=YSo go through the values and subtract 1 column value from another.


there are actually 10 columns but only 1 I need this to happen to.

Link to comment
Share on other sites

  • 1 month later...

I know you will lose some flexibility but you could make your own pivot table and within the same operation either replace your column that needs some correction or create a new one.


Add a new table, use the original one as source. Then first use a pivot transformation with sum as aggregation and then secondly calculate a new column with an additional transformation. 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...