Forum Discussion
Know how many users news repeat an order
Good morning everyone,
I'm facing a problem in my power BI model and I'm looking for some help.
I have a customer table where I have all the customer ID with every transaction (order) they made during a period. So I have one line per transaction. I also have a column to know if is the first time a customer is purchasing with a boolean of Y/N.
The problem I'm facing is that if I want to know the NEW customers I have in my table, I just need to do a calculate with a filter = Y in the column where I notice if it's the first purchase; however, I want to know how many times a NEW customer bought me in a filter date selection.
For example, a customer buys for the first time in May 2023, and makes another purchase in July 2023. I want to know, that user is new and he did 2 purchases in that period. If I apply a filter in the page to know all the users news, I miss that second purchase that have a N in the first purchase column, and I don't want to lose it.
Any ideas?
Kind regards
ATO23 , here is my suggested solution. Instead of a page-level filter, create the measures needed which do the filtering required.
I have used this set of test data:
CustomerID orderID period order_value new_customer C001 O001 01/05/2023 1234 Y C001 O002 01/07/2023 2345 N C002 O003 01/05/2023 3274 Y C003 O004 01/07/2023 2874 Y C004 O005 01/07/2023 2774 N Create a measure called [New Customers]:
New Customers = CALCULATE(DISTINCTCOUNT(YourTable[CustomerID]), YourTable[new_customer] = "Y")Create a measure called [New Customer Orders]:
New Customer Orders = VAR cust = SUMMARIZE( FILTER(YourTable, YourTable[new_customer] = "Y"), YourTable[CustomerID] ) VAR orders = CALCULATE( COUNTROWS(YourTable), ALL(YourTable), YourTable[CustomerID] IN cust ) RETURN ordersAdd a slicer to your report, using the [period] field.
Add a Card visual to your report, and drag in the [New Customers] measure.
Add a Card visual to your report, and drag in the [New Customer Orders] measure.
This gives me the following results when May-23 is selected.There are 2 new customers in May-23 (C001 and C002).
Of those two new customers, there are 3 total orders in the table (O001, 002 and O003).
This gives me the following results when Jul-23 is selected.There is 1 new customers in Jul-23 (C003).
Of that new customer, there is 1 total orders in the table (O004).
Hope this helps.
3 Replies
EylesITResolver II
ATO23 , here is my suggested solution. Instead of a page-level filter, create the measures needed which do the filtering required.
I have used this set of test data:
CustomerID orderID period order_value new_customer C001 O001 01/05/2023 1234 Y C001 O002 01/07/2023 2345 N C002 O003 01/05/2023 3274 Y C003 O004 01/07/2023 2874 Y C004 O005 01/07/2023 2774 N Create a measure called [New Customers]:
New Customers = CALCULATE(DISTINCTCOUNT(YourTable[CustomerID]), YourTable[new_customer] = "Y")Create a measure called [New Customer Orders]:
New Customer Orders = VAR cust = SUMMARIZE( FILTER(YourTable, YourTable[new_customer] = "Y"), YourTable[CustomerID] ) VAR orders = CALCULATE( COUNTROWS(YourTable), ALL(YourTable), YourTable[CustomerID] IN cust ) RETURN ordersAdd a slicer to your report, using the [period] field.
Add a Card visual to your report, and drag in the [New Customers] measure.
Add a Card visual to your report, and drag in the [New Customer Orders] measure.
This gives me the following results when May-23 is selected.There are 2 new customers in May-23 (C001 and C002).
Of those two new customers, there are 3 total orders in the table (O001, 002 and O003).
This gives me the following results when Jul-23 is selected.There is 1 new customers in Jul-23 (C003).
Of that new customer, there is 1 total orders in the table (O004).
Hope this helps.
That works perfectly! Many thanks for your help!
