Forum Discussion

dylanndengu's avatar
dylanndengu
Helper I
5 years ago
Solved

Make Customer Count Column using Distinct Values

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 th...
Need Help
Tips and Tricks
Tutorial Requests
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

     

amitchandak's avatar
amitchandak
Super User
5 years ago

dylanndengu , nor very clear , Try measure like

 

Distinctcount(Table[documentnbr])

or

 

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

  • dylanndengu's avatar
    dylanndengu
    Helper I
    5 years ago

    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.