4 Replies Latest reply on Nov 16, 2016 9:19 AM by Sherzodbek Ibragimov

    Distinct Count of Orders at a line item level

    Andrew Radil

      I am working with DATA from a cube.

       

      I am trying to find the average sales amount by sales rep. The problem is that my data set is at a line item level rather than a header level.

       

      For example, if I am looking for Tom Smith's average sales ticket amount. I have an order key that will repeat itself for each item on the sales ticket, except it adds and asterisk and a number for each line item on the ticket.

       

      Sample Orders

      2222222*1

      2222222*2

      2222222*3

       

      3333333*1

      3333333*2

      3333333*3

      3333333*4

       

      4444444*1

      4444444*2

       

      I need to wrap this to give me a DISTINCT COUNT of 3 because even though there is 9 lines, there is only 3 orders. This is my first time working with CUBES and I have come to realize that I can not write calculated fields with the ordi key dimension, nor can I even put a simple count on this. I am starting to think this needs to be done in MDX with a calculated member field.

       

      Does anyone have any thoughts?