I've been using the nz function lately, but it's starting to mess with other calculations by putting 0's in when I don't need a value in there. So i thought about using the iif function but can't really see what i'm doing wrong.
It basically goes like this: if value1 is null, then return null. if value1 is not null, then check value2/3. if value2 is null and value3 is null, then return value1. if value 2 is null and value3 is not null, then return value1-value3. if value2 is not null and value3 is null, then return value1-value2. if value1/2/3 are all not null, then return value1-value2-value3.
IIF(Canon_PG.[Ingram Price] is null,null,iif(Canon_$.[$] is null, iif([Canon_%].IM is null, null, SUM(Canon_PG.[Ingram Price]-[Canon_%].IM),iif([Canon_%].[IM] is null,SUM(Canon_PG.[Ingram Price]-[Canon_$].[$]),SUM(Canon_PG.[Ingram Price]-[Canon_$].[$]-[Canon_%].[%]))))) AS [IM TC]