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

# Distinct Count of Orders at a line item level

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?

• ###### 1. Re: Distinct Count of Orders at a line item level

Andrew,

I hope i understood it correctly. Have you tried COUND(LEFT(Name,7))

• ###### 2. Re: Distinct Count of Orders at a line item level

Please let me know if it works or not, but see pic below. i used your sample.

• ###### 3. Re: Distinct Count of Orders at a line item level

Based on your response, you are understanding what I need. However, this will not work with a calculated field because I am using a CUBE. I have to set this up through a calculated member field using MDX.

• ###### 4. Re: Distinct Count of Orders at a line item level

Andrew,

I am not expert on CUBE unfortunately. But I found some that can help you: Tableau and Cubes (OLAP) – VizPainter

Joshua Milligan. Your help is appreciated if you cans hare your thoughts.

Thanks

Sherzod