count number of customers per month where customer can buy multiple products, multiple times

[surfingjoe]

I am a newbie to Power BI. I've solved a lot of my questions with this forum, I hope to solve one more .  I can't seem to find the answer to this problem, anywhere, even though I'm sure its a common calculation.  It could be so simple, I'm not seeing the obvious answer, if so I hope someone answers anyway.

Basically I'm using SQL Query that pulls the data from multiple tables with the relationships already designed into the query.

I'm pulling in customer ID from customer table.
Product name, Product Order #, product quantity, product order date and time from Order table.

Each row will have the customer id, product name, QTY and order date and order number.  If a customer orders more than one product, then the customer ID will show up in each row for each product ordered.

I want to know the number of customers per month. I have been using distinct count of customers using a matrix by order date to calculate customers per month.  Distinct count allows me to count the customer once even though the customer orders multiple products.  Got that, but not quite accurate.

If a customer orders 5 times within a month, then I would like that to count the customer 5 times.  A distinct count will count that customer once, not sure how to distinctly count the customer for multiple products and also count the customer for multiple orders.

My question is, how can I count the customer only once when there are multiple products, but count the customer again if the customer oders products multiple times within a given time period?

[Ashish_Mathur]

The best way to get help is to share some data and show the expected result.  That one the perosn who proposes a solution can compare his/her result with yours.

[surfingjoe · Accepted solution]

The problem I have is that I'm not actually working with customers and products.   I work as an IT Director for a pharmacy and instead of customers we have patients and instead of products we have drugs and prescriptions.  For our patients protection, I cannot simply provide the data.  The way I've written the problem is hypothetically, an equivalent of what the CEO is requesting from the data.

The more I think about this problem, I'm begining to convince myself the CEO is requesting the wrong measurement.  He is trying to determine the expected revenue per customer and I realized customers have several refills per month and therefore a distinct count of customers will have the wrong count if a customer has several refills in any given time period.  It is my own hole that I'm digging when I put blinders on and try to measure exactly as the CEO has requested.

The answer to my problem is perhaps to question the CEO measurement request and change the question we are asking from the data.  I should go back to the CEO and show him that what he is asking is perhaps the wrong question of the data.  All we have to do is measure the expected revenue per "Order".  Because there is only one customer per order (in my case per presciption to fill), we will acheive the measurement he desires,by simply counting the orders, and comparing revenue per order.  Which essentially gives use revenue based on count of customers even if that customer gets multiple presciptions filled per month or multiple refills per month because there is only that one patient per presciption.  I will acheive my goal by counting the number of unique orders for any given period.

I want to thank you for your reply!  Next time, I'll use Adventureworks data to simulate my problem and expected goal as the sample data.  Or I'll provide data without patient information, as your right it would make it easier to visualize the problem.

In this case though, I think just writing the issue, expecting an answer, makes me think about the solution and come up with an obvious workaround.