Forum Discussion
Implementing an additive formula to funnel table
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 MinUserCountThe 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.
Super Usersrahmanfunlab 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
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
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
Hey BeaBF,
Unfortunately, due to confidentiality reasons, I am unable to share the PBIX file. Sample data looks something like this:
Funnel Step
Activity Funnel Step Name Activity Funnel Reporting Order ID View Activity -All 101 View Activity 102 View Activity-from Function 103 Select Activity 104 Date & Time 105 Details/Confirm 106 Extras 107 Begin Checkout 108 Payment 109 Purchase 110 User Funnel
User Pseudo ID Activity Funnel Reporting Order ID user1 101 user1 102 user1 103 user1 104 user2 101 user3 101 user3 102 user3 103 user4 101 Etc. Etc. Thanks for your help!