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













      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?