Forum Discussion
Continued ContributorCount of returning client
Hi All,
I am trying to count up client ID's when they have returned and received another application number.
The problem I have is that each application is taken through a series of events so that each application has many and varying rows.
Sample data is as follows
| Date | Cleint ID | Application Number | Sequence | Stage | Staff Memeber | Count of Applications (Desired Outcome) |
| 01.01.21 | 1 | 1 | 1 | 200 | A | 2 |
| 01.01.21 | 1 | 1 | 2 | 300 | A | 2 |
| 01.01.21 | 1 | 1 | 3 | 900 | A | 2 |
| 01.01.21 | 2 | 2 | 1 | 200 | B | 2 |
| 01.01.21 | 2 | 2 | 2 | 300 | B | 2 |
| 01.01.21 | 3 | 3 | 1 | 200 | C | 1 |
| 01.02.21 | 1 | 4 | 1 | 200 | C | 2 |
| 01.02.21 | 4 | 5 | 1 | 200 | A | 1 |
| 01.02.21 | 4 | 5 | 2 | 300 | A | 1 |
| 01.02.21 | 2 | 6 | 1 | 200 | C | 2 |
| 01.03.21 | 5 | 7 | 1 | 200 | A | 1 |
The desired output is to have the number of returning clients in a card as a number, so that a slicer of staff names changes the card. I also want to put this info into a table / matrix to show the dates and returning customers with staff name.
I hope this makes sense, it seamed easy when I started but I am a bit foxed by not being able to do this - in respsect that I get the wrong number returned due to counting the all the client ID's for each interaction!
Thanks for any help
Try:
Number of applicacions by Client ID =
Number of applicacions by Client ID = CALCULATE( DISTINCTCOUNT(Table[Application Number], ALLEXCEPT(Table, FactTable[Cleint ID]))
6 Replies
- amitchandak
Super User
DemoFour , what is the definition of returning customer?
Check these two blogs how customer retention is done
Customer 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/1377458- DemoFour
The definition of returning customer is that they have a new application number.
What I am trying to achive is a count of the number of application numbers for each client.For example
Duplicate = VAR Client = 'Application Status Change'[Client Number] VAR Result = COUNTROWS( FILTER( 'Application Status Change', AND( Client = 'Application Status Change'[Client Number] , 'Application Status Change'[Application Status Code] = 200 ) ) ) RETURN Result
This code works for the count if the application is added at 200 however we have other options (too many to code up this way) so I am looking to count each distinct application for each client (client ID) to see how many times they have had an application.
Also some new applications are opened before previous ones are closed by different members of staff, so I want this to be flagged up as this needs to stop happening.
Thank you for the links, I have read the posts and got some ideas for other things I need, but not the question I am asking.
PaulDBrownCommunity Champion
Try:
Number of applicacions by Client ID =
Number of applicacions by Client ID = CALCULATE( DISTINCTCOUNT(Table[Application Number], ALLEXCEPT(Table, FactTable[Cleint ID]))- DemoFour
PaulDBrown
Thank you, I have just got there!!Returning Client = CALCULATE( DISTINCTCOUNT( 'Application Status Change'[Application Number]), ALLEXCEPT( 'Application Status Change' , 'Application Status Change'[Client Number]) )Thank you for your post. I am just messing with the visuals to see what I can do with it.
- PaulDBrown
Happy to help!
BTW, if you want to show only "returning" clients (ie, those with more than one application) you can do this easily using the measure in the filter pane: select the table visual, go to "filters on this visual" in the filter pane, add the measure and set the value to "greater than" 1
