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

    Cross table showing average and null values


      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




      Today, I have this:


      Coca ColaNormal
      Coca ColaZero
      Coca ColaLight



      Market 1 Price



      Market 2


      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!