# Query for Average Distinct Per Document

• ### Question

• Hi,

I have table with Customer, Store, Document, Product, Qty.

I want to a calculated measure which would return Average number of distinct products per document in any context.

How do I write that?

Wednesday, November 28, 2012 2:06 PM

• I think the following measure should do the trick

`=AVERAGEX(values(Fct[Document]), calculate(DISTINCTCOUNT(Fct[Product])))`
See the example below:-

At C1 level, it has got

Prods for D1 (3) + D2 (2) + D3 (1) + D4 (1) / 4 = 1.75

Cheers,
Jason | www.SqlJason.com

• Marked as answer by Thursday, November 29, 2012 7:46 AM
Wednesday, November 28, 2012 4:58 PM

### All replies

• Hi algkep -

Believe you can accomplish this by grouping by document with a SUMMARIZE like this:

`=DISTINCTCOUNT([Product])`
`=AVERAGEX(SUMMARIZE(Sales,Sales[Document],"DistProd",[DistinctCountProduct]),[DistProd])`
Let me know if that helps.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

Wednesday, November 28, 2012 3:04 PM
• I think the following measure should do the trick

`=AVERAGEX(values(Fct[Document]), calculate(DISTINCTCOUNT(Fct[Product])))`
See the example below:-

At C1 level, it has got

Prods for D1 (3) + D2 (2) + D3 (1) + D4 (1) / 4 = 1.75

Cheers,
Jason | www.SqlJason.com

• Marked as answer by Thursday, November 29, 2012 7:46 AM
Wednesday, November 28, 2012 4:58 PM
• both of these seem to do what I want! I actually came up with these aswell, but was misinterpreting my data.

Thanks guys, you're great!

Thursday, November 29, 2012 7:46 AM
• algkep -

Glad they both work for you.  Both are accomplishing the same thing by grouping on product.  Just slightly different syntax.  And I used a separate measure for the distinctCount to simplify and to be able to show the components of the calc next to each other when testing.  Regardless, same result.