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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Summarise my data by count into categories by unique count by month

Hi there,

 

I have website data including [Unique ID] and [Login Date]. I want to group my data by checking how many times a unique user logged in over a month, and categorising the count by:
'Not engaged' = 0 logins
'Low engagement' = 1-3 logins

'Moderate engagement' = 4-6 logins

'High engagement' = >6 logins 

 

How do I do this?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Jemma,

 

Yes - you can have measures like "Average views per day" or "Average views per month" etc. all calculated from and in the same table.

 

To be accurate, you'd have to calculate this based on the total number of days/months/years a user has been 'active'. Do you have a "User created date" or something similar?

 

Matt

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Assuming your [Unique ID] column refers to a user not an individual login to the site, you can achieve the desired result using the below steps:

 

  1. Select the [Unique ID] Column and click "Group by" in the ribbon.
  2. Ensure that you are grouping by [Unique ID].
  3. Click "OK"
  4. Create a new column ("Custom Column") and call it something like "Engagement Level".
  5. Put an if statement in the formula along the lines of: if [Count]=0 then "Not engaged" else if [Count]<3 then "Low engagement" else if [Count] < 6 then "Moderate engagement" else "High engagement"
  6. Click OK

Does that give you what you are after?

 

Happy to elaborate if you get stuck!

 

Matt

Anonymous
Not applicable

Hi Matt,

 

Yep this totally works, but I need engagement to be relative to time as well. 

For example, someone engaged 7 times in a day that would be high engagement, but if it's 7 times in a year, this isn't high.


So, I can include date in the grouping, i've had a go at separate tables for engagement by day, month, year. I wondered if it was possible to do all this in one table, or as a dynamic measure?

 

Thanks! 🙂

Jemma

Anonymous
Not applicable

Hi Jemma,

 

Yes - you can have measures like "Average views per day" or "Average views per month" etc. all calculated from and in the same table.

 

To be accurate, you'd have to calculate this based on the total number of days/months/years a user has been 'active'. Do you have a "User created date" or something similar?

 

Matt

Anonymous
Not applicable

Ahhhh I totally see your point. Yes, I can pull in the User's registration date, and do the averages. You're a genius!

 

Thanks so much Matt!! 🙂 

 

Jemma

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.