Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
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

Screenshot 2019-08-19 11.58.23.png

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!

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
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' ) )

Capture29.JPG

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.

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
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

v-juanli-msft
Community Support
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' ) )

Capture29.JPG

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
Super User
Super User

Hi,

What exat result are you expecting and why?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
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.

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.