2 Replies Latest reply on Jun 30, 2016 7:39 AM by audreyd

    Cross table showing average and null values

    audreyd

      Dear all,

       

      I am currently working on a report which shows me the average price of a product based on location.

       

      The idea being to compare the price of products across different markets.

       

      What I would like to do is this: the user specifies the product and subproduct he would like to see the price for (for instance, product: Coca Cola, subproduct: Zero, random example I just made up).

       

      Then a table shows me the price of the Coke Zero price for a market I can chose.

       

      Now here is the tricky part. When I have for instance a market that does not have Coke Zero, it will not show any value. Which makes sense as there is no average. What I would like to have is for these case have a null or 0 value.

       

      The user can chose which market (and other criteria per market). As such, Market 1 has its own sets of filters and Market 2 also. Because of this, both columns are actually 2 different reports in my workbook.

      For the Product and subproduct, they are also 1 report (I didn't want to have these lines displayed for each market).

       

      In the end my dashboard would have:

      ProductSubproductMarket 1 PriceMarket 2
      Coca ColaNormal$1$1.5
      Coca ColaZero0$1.5
      Coca Cola

      Light

      $1.3$1.3

       

      Today, I have this:

       

      ProductSubproduct
      Coca ColaNormal
      Coca ColaZero
      Coca ColaLight

       

       

      Market 1 Price
      $1
      $1.3

       

       

      Market 2
      $1.5
      $1.5
      $1.3

       

      As you can see Market 1 Price is just not displaying anything for Coca Cola Zero because it is not selling.

       

      Do you have any idea how I can proceed?

       

      Thanks a lot!

      Cheers!