cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NabihaF
Frequent Visitor

Calculating cumulative number of sessions per unique user.

This is what my data looks like:

NabihaF_0-1656421918546.png

 

I have unique user IDs, and the number of sessions for that ID on a given date.

Now i want to create a basic line chart which can show the cumulative number of sessions per unique user.

And a distribution of all the unique users as per the number of sessions they have had.

 

Please help.

 
4 REPLIES 4
NabihaF
Frequent Visitor

I would like to explain this query a bit further. For example, this is my sample data:

NabihaF_0-1656427517331.png

Now out of this data, i need to see the following:

Unique User CountTotal Sessions
117
112

And also this data across any selected time period:

NabihaF_2-1656427583796.png

@tamerj1 @amitchandak  , would really appreciate your support.

 

HI @NabihaF,

In fact, I think you can use the default visual aggregate feature to achieve your requirement.
You can create a table visual with the date(switch to hierarchy mode and keep year, and month levels), add the 'user id' field with aggregate mode 'distinct count', and add the session field with 'count' mode.

Work with aggregates (sum, average, and so on) in Power BI - Power BI | Microsoft Docs

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello,

 

Thank you for your response. but this does not really give me what i need.

I can see aggregated data for all users:

NabihaF_0-1656658275642.png

I have managed to 'Group by' user ID, to get what i want, but i have to remove the date column, otherwise, i dont get the sum of sessions by user ID. I still need to add the date somehow.

This is what i have so far:

NabihaF_1-1656658371268.png

 

HI @NabihaF,

Did you mean to get the calculated result that aggregated based on particular field groups without adding them to the current table visual?
If that is the case, you can try to create a variable with summarize function to group current records with specific fields. After these, you can use the iterator function to aggregate these summarized results.

Measure Totals, The Final Word 

 

formula1 =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table ),
        [Year],
        [Month],
        [User id],
        "Session Count", COUNT ( Table[Session] )
    )
RETURN
    SUMX ( summary, [Session Count] )

 

For the second visual effect, you need a new parameter table with number fields to use as the axis. Then you can use the following measure formula to show the results based on the 'user count' that calculates by date ranges:

 

formula2 =
VAR currNumber =
    MAX ( NewTable[Number] )
VAR summary =
    GROUPBY (
        SUMMARIZE (
            Table,
            [Year],
            [Month],
            [User id],
            "Session Count", COUNT ( Table[Session] )
        ),
        [Session Count],
        "User Count", COUNTX ( CURRENTGROUP (), [User id] )
    )
RETURN
    MAXX ( FILTER ( summary, [User Count] = currNumber ), [Session Count] )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors