Forum Discussion

srahmanfunlab's avatar
srahmanfunlab
New Member
1 year ago
Solved

Implementing an additive formula to funnel table

Hey guys, I'm trying to set up a funnel report where there is a table that progresses through the funnel steps and shows the number of users that progress through each step. A sample can be seen as below:

Assuming that the userflow is a single path, no step should have a higher count than the step/steps prior. So to solve this problem, I want to include a catch in my measure where if the next step has a higher count than the current step, the current step should have an equal count to the next step. So in the example provided above, "Payment" should equal "Purchase" and show a total count of 128 rather than 119.

 

Here is the DAX measure that I created, but I can't help but feel that I'm missing a fundamental piece of logic. The Activity Funnel Reporting Order ID is an integer column that dictates the ascending order in which the Funnel Steps are displayed and the User Pseudo ID is a total number of unique users that we're tracking that land on each funnel step.

Total User V2 = 
VAR CurrentOrderID = MAX('Funnel Step'[Activity Funnel Reporting Order ID])
VAR MaxOrderID = CALCULATE(
    MAX('Funnel Step'[Activity Funnel Reporting Order ID]),
    ALL('Funnel Step')
)
RETURN 
CALCULATE(
    DISTINCTCOUNT('User Funnel'[User Pseudo ID]),
    FILTER(ALL('Funnel Step'[Activity Funnel Reporting Order ID]), 'Funnel Step'[Activity Funnel Reporting Order ID] >= CurrentOrderID)
)

 

What am I missing? Your help would be appreciated!

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi srahmanfunlab ,

    Based on the testing, try using the following DAX formula.

     

    Total User = 
VAR CurrentOrderID = MAX('Funnel Step'[Activity Funnel Reporting Order ID])
VAR MaxOrderID = CALCULATE(
    MAX('Funnel Step'[Activity Funnel Reporting Order ID]),
    ALL('Funnel Step')
)
VAR Steps =
    SUMMARIZE(
        FILTER(
            'Funnel Step',
            'Funnel Step'[Activity Funnel Reporting Order ID] <= CurrentOrderID
        ),
        'Funnel Step'[Activity Funnel Reporting Order ID],
        "UserCount", DISTINCTCOUNT('User Funnel'[User Pseudo ID])
    )
VAR MinUserCount = 
    MINX(
        Steps,
        [UserCount]
    )
RETURN 
    MinUserCount

     

    The result is shown below.

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    1 year ago

    srahmanfunlab Hi! try with:

     

    Total User V2 =
    VAR CurrentOrderID = MAX('Funnel Step'[Activity Funnel Reporting Order ID])
    VAR FunnelSteps =
    ADDCOLUMNS(
    ALL('Funnel Step'),
    "AdjustedCount",
    CALCULATE(
    DISTINCTCOUNT('User Funnel'[User Pseudo ID]),
    'Funnel Step'[Activity Funnel Reporting Order ID] >= MAX('Funnel Step'[Activity Funnel Reporting Order ID])
    )
    )
    VAR AdjustedValues =
    ADDCOLUMNS(
    FunnelSteps,
    "CumulativeAdjustedCount",
    VAR PrevStepAdjustedCount =
    MAXX(
    FILTER(
    FunnelSteps,
    [Activity Funnel Reporting Order ID] < EARLIER('Funnel Step'[Activity Funnel Reporting Order ID])
    ),
    [AdjustedCount]
    )
    RETURN IF(PrevStepAdjustedCount > [AdjustedCount], PrevStepAdjustedCount, [AdjustedCount])
    )
    RETURN
    MAXX(
    FILTER(
    AdjustedValues,
    [Activity Funnel Reporting Order ID] = CurrentOrderID
    ),
    [CumulativeAdjustedCount]
    )

     

    BBF

    • srahmanfunlab's avatar
      srahmanfunlab
      New Member
      1 year ago

      Hey BeaBF! Thanks for such a quick response, I've tried your solution and it seems to populate the final step in the funnel but leaves the rest as blank.

      I've tried a more basic breakdown level of logic but it also gives me a completely blank column sadly.

      Total Users V2 = 
VAR CurrentStep = SELECTEDVALUE('Funnel Step'[Activity Funnel Reporting Order ID])
VAR CurrentUsers = CALCULATE(DISTINCTCOUNT('User Funnel'[User Pseudo ID]), 'Funnel Step'[Activity Funnel Reporting Order ID] = CurrentStep)
VAR PreviousStep = 
    MAXX(
        FILTER(
            'Funnel Step',
            'Funnel Step'[Activity Funnel Reporting Order ID] < CurrentStep
        ),
        'Funnel Step'[Activity Funnel Reporting Order ID]
    )
VAR PreviousUsers = 
    CALCULATE(
        DISTINCTCOUNT('User Funnel'[User Pseudo ID]),
        'Funnel Step'[Activity Funnel Reporting Order ID] = PreviousStep
    )
RETURN
    IF(CurrentUsers > PreviousUsers, PreviousUsers, CurrentUsers)
)

       

      Thanks for your help, please share if you have any other ideas!

      • BeaBF's avatar
        BeaBF
        Icon for Super User rankSuper User
        1 year ago

        srahmanfunlab Try:
        Total Users V2 =
        VAR CurrentStep = SELECTEDVALUE('Funnel Step'[Activity Funnel Reporting Order ID])
        VAR CurrentUsers =
        CALCULATE(
        DISTINCTCOUNT('User Funnel'[User Pseudo ID]),
        'Funnel Step'[Activity Funnel Reporting Order ID] = CurrentStep
        )
        VAR PreviousStep =
        CALCULATE(
        MAX('Funnel Step'[Activity Funnel Reporting Order ID]),
        FILTER(
        'Funnel Step',
        'Funnel Step'[Activity Funnel Reporting Order ID] < CurrentStep
        )
        )
        VAR PreviousUsers =
        IF(
        NOT ISBLANK(PreviousStep),
        CALCULATE(
        DISTINCTCOUNT('User Funnel'[User Pseudo ID]),
        'Funnel Step'[Activity Funnel Reporting Order ID] = PreviousStep
        ),
        0
        )
        RETURN
        IF(CurrentUsers > PreviousUsers, PreviousUsers, CurrentUsers)

        it's difficult to help you without the pbix, can you share it? or provide sample data and the expected output

         

        BBF

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi srahmanfunlab ,

    Based on the testing, try using the following DAX formula.

     

    Total User = 
VAR CurrentOrderID = MAX('Funnel Step'[Activity Funnel Reporting Order ID])
VAR MaxOrderID = CALCULATE(
    MAX('Funnel Step'[Activity Funnel Reporting Order ID]),
    ALL('Funnel Step')
)
VAR Steps =
    SUMMARIZE(
        FILTER(
            'Funnel Step',
            'Funnel Step'[Activity Funnel Reporting Order ID] <= CurrentOrderID
        ),
        'Funnel Step'[Activity Funnel Reporting Order ID],
        "UserCount", DISTINCTCOUNT('User Funnel'[User Pseudo ID])
    )
VAR MinUserCount = 
    MINX(
        Steps,
        [UserCount]
    )
RETURN 
    MinUserCount

     

    The result is shown below.

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.