cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NGTaylor
Frequent Visitor

Counting Stages of Records

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:

  • Enquiry
  • Marketing Qualified Lead
  • Sales Qualified Lead
  • Sales Accepted Lead
  • Rejected

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.

 

 

Lead Table.JPG

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@NGTaylor,

 

Add a calculated column to return 1, 2, 3, 4 and 0 for each stage, then you may take advantage of the maximum value.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@NGTaylor,

 

Add a calculated column to return 1, 2, 3, 4 and 0 for each stage, then you may take advantage of the maximum value.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors