Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm desperate for help on counting status stages of Leads if anybody could help.
I have a table of leads with Lead Number, Created Date, Lead Status, No. days in that status, Lead Tracking Status, Country, Business Unit and Campaign Name. I'd like to create a measure (I think) that counts the number of leads that have been in each status. The difficulty arises when I need to count each lead in its status, even if the system didn't record that stage.
There are four sequential status stages, with Rejected a non-sequential stage:
The end result is either Sales Accepted Lead or Rejected and some leads will be in progress, ie: at stage 1, 2 or 3.
If the result is SAL, then I would want to record a count in each of the three previous stages, even if it doesn't exist as a row. For a lead that has been properly Sales Accepted, it should count as an Enquiry, a Marketing Qualified Lead, a Sales Qualified Lead and a Sales Accepted Lead but if it only has two rows (eg: Enq and SAL or MQL and SAL) I would still want to count it in each stage.
If the result is Rejected, then it should count against the stages that have been recorded or any earlier stages not recorded but should have been. ie: If Lead Number 1 has rows for Enquiry and Rejected, then it should only count in Enquiry. If Lead Number 2 has rows for Enq, SQL and Rejected, then it should count in Enq, MQL and SQL.
Sorry for the confusing description. Screenshot of the table is below.
I've tried a few things but nothing gives me the right results.
Solved! Go to Solution.
Add a calculated column to return 1, 2, 3, 4 and 0 for each stage, then you may take advantage of the maximum value.
Add a calculated column to return 1, 2, 3, 4 and 0 for each stage, then you may take advantage of the maximum value.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |