3 Replies Latest reply on Jan 19, 2012 8:13 PM by deepamathur0

    Dealing with Nulls when aggregating blended measures

    Michael Mixon

      Hi,

       

      I've got three data sources that I have blended together.  Each source contains a different cost measure for a product.  Creating a single view that shows all products and each of the three costs (where available) is easy...but now I'd like to add the three costs up for a new "total cost" measure.  The problem is that when one of the costs is null, the Total Cost measure ends up being null as well.  What I want is for the Total Cost calculation to add up all available cost measures, so if a product only has two costs available (i.e. there is no row in Source 3), then the Total Cost measure would simply be the sum of costs from Source 1 and Source 2.

       

      Is there a way to do this.  I've tried using the ISNULL, IFNULL and ZN functions, but none of them appear to work.

       

      Thanks.

       

      -Mike