Make Customer Count Column using Distinct Values

[dylanndengu]

Hi Everyone,

I am having a bit of trouble creating a customer count column based on the below information:

Essentially, I would like a column where customer count is equal to 1 if the document number (last column) has a distinct number on a specific "report date" and "sold date". Please may someone assist.

Kind regards,

Dylan

[Tahreem24]

@dylanndengu  Not really cleare. Based on my understanding you may create the below measure:

Measure = CALCULATE(DistinctCount(TableName[DocumentNumber]),ALLEXCEPT(TableName,TableName[ReportDate]))

[dylanndengu]

Apologies for the lack of clarity. The data I have takes into account everything a customer has bought on a specific day and assigns that transaction a number (documnetnr). The issue is if one customer buys a soda in addition to a bucket of chicken, those are recorded as 2 different transactions with the same document number. Hence, in order to calculate the actual number of customers, I need to only count distinct document numbers on a specific day, as the document numbers generated restart every 24 hours. I hope that clarifies things a bit.

[v-deddai1-msft · Accepted solution]

Hi @dylanndengu ,

You can use the following measure :

Measure = SUMX(SUMMARIZE('Table','Table'[report date],'Table'[sold date],"countofcustomer",DISTINCTCOUNT('Table'[document])),[countofcustomer])

Sample data:

Result:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[dylanndengu]

This is great, thank you so much

[amitchandak]

@dylanndengu , nor very clear , Try measure like

Distinctcount(Table[documentnbr])

or

Countx(summarize(Table, Table[reportdate], table[soldate], "_1",Distinctcount(Table[documentnbr])),[_1])

[dylanndengu]

Hi Amit,

Thank you for responding but I do not thing the solution will work, and it is probably due to my lack of clarity.

The data I have takes into account everything a customer has bought on a specific day and assigns that transaction a number (documnetnr). The issue is if one customer buys a soda in addition to a bucket of chicken, those are recorded as 2 different transactions with the same document number. Hence, in order to calculate the actual number of customers, I need to only count distinct document numbers on a specific day, as the document numbers generated restart every 24 hours. So you can't simply take a distinct count of the document numbers because new ones are generated every day. I'm thinking I could perhaps group by report date, sold date and document number and then do a distinct count on that? Please let me know what you think. I hope that clarifies things a bit.