6 Replies Latest reply: Jun 10, 2016 5:10 AM by Pekka Laaksonen RSS

    summarize by one dimension in two dimension table

    Pekka Laaksonen



      I have two tables in my data model, one contains purchases from suppliers and contains sales offers to clients. My client buys products from suppliers and those products are then offered to clients. Between these two tables, only thing in common is the product number. So I cannot link purchases to sales offers and therefore tables are concatenated instead of joining them.


      Now, I need to a report which has purchases and per supplier and product and sales offer success ratio per product. So my table looks something like this:


      Common fieldOnly in purchasesOnly in purchasesOnly in purchasesOnly in sales offers
      ProductSupplierAmountQtyHit Ratio
      Product 1Supplier A10010
      Product 1 0010 %
      Product 2Supplier A20020
      Product 2 0020 %
      Product 3Supplier A30030
      Product 3 0030 %


      Now I'd like to get that Hit Ratio on same row as Amount and Qty. Problem is that Sales Offers is not present in Sales offers table and therefore I get two rows. I tried using Aggr function (=aggr(sum([Hit Ratio], Product) but that seemed to work only when I had Product selected.


      Is there a way of getting that Hit Ratio on same row in chart?