Measure to calculate percentage of user bounce rate

[Anonymous]

Hi there,

I am new to Power BI and need some help with calculating a few measures from a table that holds the state of a user, one of which is the new user bounce rate.

User States Table:

UserID | State | Date

The above table holds all state changes for all users.

Here my current version of the measure, which divides the users that have had the state 2 by all users to get the %.

New User Bounce Rate = 
    DIVIDE(
        CALCULATE(DISTINCTCOUNT(UserStates[UserId]), FILTER(UserStates, UserStates[State] = 2)),
        CALCULATE(DISTINCTCOUNT(UserStates[UserId]))
    )

The issue with the above measure is that it is also including users that had state = 2, but are now in a different state - which should not be included in s. So what I would like to do is (somehow) only include UserIds in the first distinct count, where the current state = 2 or in other words where the row with the max date has state = 2 for that UserId.

Thanks for your help in advance!

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures as below

Max date = CALCULATE(MAX('Table'[date]),ALLEXCEPT('Table','Table'[user id]))

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[user id] ),
    FILTER ( ALL ( 'Table' ), 'Table'[date] = [Max date] && 'Table'[state] = 2 )
)
    / CALCULATE ( DISTINCTCOUNT ( 'Table'[user id] ), ALL ( 'Table' ) )

Best Regards
Maggie

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

[Ashish_Mathur]

Hi,

What exat result are you expecting and why?

[Anonymous]

I would like to get the percentage of users (UserIds) that currently have the state = 2. The only way to identify if the user currently has the state is by checking if the user has the sate = 2, but also by checking if the state assign date is the MAX for that user across all states that have been assigned to that user. So, for example, the user with ID 278588 should be included in that calculation, whereas a user that has a user that had been in state 2, but the table holds another record for that user with a state assign date > the state assign date for state = 2, should not be included.

[Ashish_Mathur]

Hi,

Share data in a form that can be pasted in MS Excel.