Complex Calculation

[Stuart Wise]

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

case

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

(case

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

end)

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,

Stuart

[Stuart Wise]

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,

[Kirsten Smith (she/her)]

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

[Stuart Wise]

Hi,

 

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.

[Fabian Duerr]

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.