2 Replies Latest reply: Mar 28, 2016 11:35 PM by AC BC RSS

    Sum IF with expressions

    Tom Dabek

      I have a chart (straight table) that displays:

       

      Source (Dimension)

      Product (Dimension)

      ListPrice (Expression - this is a value from a table)

      Volume (Expression)

      ASP (Expression - this is a calculation [Revenue]/[Volume])

       

      There can be many rows of data returned, but there is a ListPrice for all combinations of Sources and Products.

      I want to add a columns that are expressions that will show the volume for all records where the ASP is less than the ListPrice (or above ListPrice).

       

      I've tried these but they don't work, the sum of the two columns is less than the total volume shown.

       

      sum(if(Revenue/Volume<ListPrice,Volume))

       

      sum(if(Revenue/Volume>=ListPrice,Volume))


      This seems like a simple problem, but I cannot figure out how to create an expression that will show the value I am trying to display.