neadbecker
New Member
- Joined
- Jan 15, 2009
- Messages
- 43
I have a data set with a list of transactions made by 30k customers this year. Each customer might make anywhere from 1 to dozens of transactions a year. I would like to calculate a daily average of the number of customers who make a transaction on each day of the year. (not worried about how many transactions are made) Some customers may make several transactions a day and some customers may make only a few transactions a month.
I'm trying to figure out how to uniquely count a customer on a day, but count them every day they make a transaction. So far, if I use CountRows(Distinct(customerid)), I get the right count in a pivot table by day, but when i aggregate by week, month, year, i get the total unique customers making a transaction for those time periods.
I figured it must be something with AverageX or Countx Combined with VALUES(), but I just can't get my head around it.
Excel 2013, 64bit
I'm trying to figure out how to uniquely count a customer on a day, but count them every day they make a transaction. So far, if I use CountRows(Distinct(customerid)), I get the right count in a pivot table by day, but when i aggregate by week, month, year, i get the total unique customers making a transaction for those time periods.
I figured it must be something with AverageX or Countx Combined with VALUES(), but I just can't get my head around it.
Excel 2013, 64bit
Last edited:
