Calculating cumulative number of sessions per unique user.

[NabihaF]

This is what my data looks like:

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.

[NabihaF]

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

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

Unique User Count	Total Sessions
1	17
1	12

And also this data across any selected time period:

@tamerj1 @amitchandak  , would really appreciate your support.

[v-shex-msft]

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

[NabihaF]

Hello,

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

I can see aggregated data for all users:

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:

[v-shex-msft]

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