3 Replies Latest reply on Feb 12, 2019 4:33 AM by Petia Rangelova

# calculated fields with coutnd/sales funnel

Hi

Attached is a workbook for orders and sales phases. I need to count how many orders are in each of the phases, represent per selling line and calculate conversion % form phase 1 to phase 2, phase 2 to phase 3. The thing is that for some order ids there is more than one line, so I would need to count per phase with a distinct count of order ids.

Here is what I have in the workbook and what I am trying to figure out:

I have the funnel displayed showing number of orders per phase. I get the same number of orders for each line (which is the total). How can I see the funnel per selling line with correct count?

I have done the calculated fields with conversions as per this formula:

{FIXED Stage:COUNTD(if Stage='Phase 2' then [Order number] END)}/{FIXED Stage:COUNTD(if Stage='Phase 1' then [Order number] END)}

How can I display these on the funnel or a separate sheet? I only see zeros?

How can I calculate the % of all order numbers in phase 3 that have reached status 'won' in that phase 3? (there are repetitions in order number and status in phase 3 can be 'won', 'lost' or open)

Thanks!

• ###### 1. Re: calculated fields with coutnd

Please upload a sample workbook.  There are too many variables that could impact this answer, and an actual workbook will let someone look at those variables so that you can have a complete answer.

• ###### 2. Re: calculated fields with coutnd

You need to Write Few LODs

I need to count how many orders are in each of the phases:

{FIXED Stage:COUNTD(ORDER NUMBER)}

phase 1 to phase 2 Conversion:

{FIXED Stage:COUNTD(if Stage=Phase1 then ORDER NUMBER END)}/{FIXED Stage:COUNTD(if Stage=Phase2 then ORDER NUMBER END)}

phase 2to phase 3 Conversion:

{FIXED Stage:COUNTD(if Stage=Phase2 then ORDER NUMBER END)}/{FIXED Stage:COUNTD(if Stage=Phase3 then ORDER NUMBER END)}

Thanks

Deepak

• ###### 3. Re: calculated fields with coutnd/sales funnel

Thanks, Deepak.

I have applied your formula and attached a workbook as I still face some issues.

The initial message is edited with the questions I have.