Calculating the average number of distinct values in a column

[Anonymous]

Hi there,

I have data that looks similar to this:

Purchase Order Number:                       Vendor Name:

001                                                          Vendor A

002                                                          Vendor B

003                                                          Vendor B

004                                                          Vendor C

005                                                          Vendor C

006                                                          Vendor C

Each purchase order number is a distinct value, but a vendor can have multiple purchase orders. So with this data I am trying to find the average number of purchase orders per vendor. In this case, I would be looking for a DAX formula that would return the answer of 2, since the average count of POs that a vendor has would be 2. Any help is greatly appreciated.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures

distinctount = CALCULATE(DISTINCTCOUNT('Table'[Purchase Order Number]),ALLEXCEPT('Table','Table'[Vendor Name]))

average = AVERAGEX(DISTINCT('Table'[Vendor Name]),[distinctount])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

If my understanding is correct, you can use the following formula

Measure = DIVIDE(count('Table'[Order Number]),DISTINCTCOUNT('Table'[Vendor Name]))

Eric Ji | Senior Business Intelligence Consultant
www.designmind.com