Forum Discussion

hamadani's avatar
hamadani
Frequent Visitor
4 years ago
Solved

Delta Funnel

Hi,

 

I have a following sample table:

Page NameCustomer ID
Page ACustomer 1
Page ACustomer 2
Page BCustomer 1
Page BCustomer 2
Page BCustomer 3
Page BCustomer 4
Page CCustomer 1
Page DCustomer 1
Page DCustomer 2

 

From the above table, I have created a funnel which shows the unique number of customers per page

Page NameNo. Unique Customers
Page A2
Page B4
Page C1
Page D

2

 

The funnel is sorted automatically as shown below (with many more page names that I have in my source data):

 

My question is how can I calculate or depict the number of dropped customers as they travel from most visited page (page B) to least visited page (page C) in Power BI:

 

Page NameNo. Dropped Customers
Page B 
Page A2 (4 in Page B - 2 in Page A)
Page D0  (2 in Page A - 2 in Page D)
Page C

1  (2 in Page D - 1 in Page C)

 

In other words, how can I visulaise the delta funnel?

 

Many thanks

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, hamadani 

    As a workaround, you can  try the following steps to visulaise the delta funnel.

    1. add a summarize table:
    calculated table:

    Table 2 = SUMMARIZE('Table','Table'[Page Name],"No.Unique Customers",DISTINCTCOUNT('Table'[Customer ID]))

    calculatd column:

    Rank = RANKX(ALLSELECTED('Table 2'),'Table 2'[No.Unique Customers],,DESC,Dense) 
    Rankvalue = 
RANKX (
    'Table 2',
    'Table 2'[Rank]
        + RANKX ( 'Table 2', 'Table 2'[Page Name],, ASC, DENSE )
            / CALCULATE ( COUNTROWS ( 'Table 2' ), ALLSELECTED () ),
    ,
    ASC,
    DENSE
)
    No.Dropped Customers = 
VAR a = 'Table 2'[No.Unique Customers]
VAR b =
    CALCULATE (
        MAX ( 'Table 2'[No.Unique Customers] ),
        FILTER (
            'Table 2',
            'Table 2'[Rankvalue]
                = EARLIER ( 'Table 2'[Rankvalue] ) - 1
        )
    )
RETURN
    IF ( ISBLANK ( b ), BLANK (), b - a )

    2. consider using custom tooltip page to show the Dropped Customers.
    Result:

    Best Regards,
    Community Support Team _ Eason

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    Hi, hamadani 

    As a workaround, you can  try the following steps to visulaise the delta funnel.

    1. add a summarize table:
    calculated table:

    Table 2 = SUMMARIZE('Table','Table'[Page Name],"No.Unique Customers",DISTINCTCOUNT('Table'[Customer ID]))

    calculatd column:

    Rank = RANKX(ALLSELECTED('Table 2'),'Table 2'[No.Unique Customers],,DESC,Dense) 
    Rankvalue = 
RANKX (
    'Table 2',
    'Table 2'[Rank]
        + RANKX ( 'Table 2', 'Table 2'[Page Name],, ASC, DENSE )
            / CALCULATE ( COUNTROWS ( 'Table 2' ), ALLSELECTED () ),
    ,
    ASC,
    DENSE
)
    No.Dropped Customers = 
VAR a = 'Table 2'[No.Unique Customers]
VAR b =
    CALCULATE (
        MAX ( 'Table 2'[No.Unique Customers] ),
        FILTER (
            'Table 2',
            'Table 2'[Rankvalue]
                = EARLIER ( 'Table 2'[Rankvalue] ) - 1
        )
    )
RETURN
    IF ( ISBLANK ( b ), BLANK (), b - a )

    2. consider using custom tooltip page to show the Dropped Customers.
    Result:

    Best Regards,
    Community Support Team _ Eason