locked
Query for Average Distinct Per Document RRS feed

  • 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

Answers

  • 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
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

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

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
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Wednesday, November 28, 2012 3:04 PM
    Answerer
  • 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
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by algkep Thursday, November 29, 2012 7:46 AM
    Wednesday, November 28, 2012 4:58 PM
    Answerer
  • 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. 

    Please mark correct answers.  Thanks.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Thursday, November 29, 2012 1:59 PM
    Answerer