2 Replies Latest reply: Jul 19, 2016 4:25 AM by James Hanify RSS

    Pivot table split up

    James Hanify

      Hi,

       

      I don't know whether this is possible but this is what I would like to achieve.

       

      Format / Example Data of how I would like it to look like is:

       

      CustomerCustomer NameSectorYears with companyNumber of orders

      Revenue

      ServicesProductBothTotalServicesProductTotal
      1AZZ12226100200300
      2BYY2111350200250
      3CZZ321147090160

       

      The best i've been able to do is based on the attachment, using an if statement to create the difference between a product and a service, as the if statement to count the revenue didn't seem to work when I removed that dimension, it would just assigned it to the first one.

       

      I'm also unsure about the product and services on how to do this as the data structure is

       

      Order

       

      Order number (o1234)

       

      Order Details

       

      Order Number, Item (x1234)

       

      ItemDetails

       

      Item, Item Name, Product Type (apple)

       

      I have then been doing an if statement to say if product type = service, its a service, if it isn't its a product.

       

      Hopefully you can provide some input even if I am being a bit vague.

       

      Many thanks,

       

      james