Forum Discussion
Calculating Customer Status column and measures
I have two tables, Company_customers and Payments. I need to calculate the customers that have made payment(s) in the respective company. Discriminating users between Active and Passive users.
I need to calculate a column that returns if the company customer is a Paying User or a Non-Paying User, in the company_customer table. As well as the Quantity of payments the user has done, and the value.
5 Replies
- amitchandak
Super User
spoten , think customer Retention lost cutomer logic should help
Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458I had a look at the links you sent. But I could not serve myself with a solution, based on that. Also, I could not figure out the [Sales], in your mentioned post, if it is a Value ($) field, or a Quantity field, as you do not show a print screen of Sales tables.
Can you custom the calculated measures or columns for my specific situation?
- tamerj1
Community Champion
I guess the user ID's are unique in the Company_customers table. If so you can easily ctreate a relationship between the two tables. The is not as complicated as you might expect (hopping that date has nothing to do with your calculation).
Use can use the following codes to create the three calculated columns in the Company_customers tablePaying User? = NOT ISBLANK ( RELATEDTABLE ( Payments ) )Number of Payments = COUNTROWS ( RELATEDTABLE ( Payments ) )Amount Paid = SUMX ( RELATEDTABLE ( Payments ), Payments[Valor] + Payments[Valor_Cash] )Hi tamerj1, actually I need to know if and when a user has made a payment in a respective company.
I believe the first step is to add a "User status" column in the Company_customer table.
For example:
01/01/2001 - User1 entered Company1 customer base -> user status = "Passive Customer"
03/01/2001 - User1 made a payment for Company1 -> user status = "Paying Customer"
03/01/2001 - User1 entered Company2 customer base -> user status = "Passive Customer"
From there, we could calculate the measures for Paying Customers and Non-Paying Customers easily.
