cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Measure to calculate percentage of user bounce rate

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.

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Community Support

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

Community Support

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.

Super User

Hi,

What exat result are you expecting and why?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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.

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com