Group by and count in scatter plot

[Bart_Berg]

Hi guys,

I really hope someone is able to shed some light on this challenge I am tasked with.

I am trying to build a scatter plot to visualize some data.

My data looks as follows:

Sessions:

date

user_id

content_id

id

Date	user_id	content_id	id
2021-10-14	5	1	1
2021-10-14	8	1	2
2021-10-14	8	1	3
2021-10-14	8	6	4

Now, I need to build a scatter plot that visualizes the different amount of content that is consumed in a month by users. So, in the table above we can see that user 5 consumes 1 unique content item this month and user 8 consumes 2 unique content items.

x-axis - count of id (so that is the session count).

y-axis - the count of unique content_ids per user

size - distinct count of user_id.

I have absolutely no idea how I can group the data on the y-axis to visualize this.. In this example I would expect two bubbles in my chart. 1 bubble with an y-axis value of 1, and one bubble with an y-axis value of 2.

I hope anyone can point me in the right direction :)!

[mahoneypat]

This should be possible without a calculated table.  Please clarify what each bubble represents.  Is each one a User?  a month?  You gave the expect Y axis values.   What are the expected values for the X axis and bubble size?  I thought each bubble was a user until I saw that size was distinct count of users.

Pat

[AlexisOlson]

X-axis is the session count for a user. Size is the number of users with this session count. If you have three users and one has 3 sessions and the other two have 5 sessions each, then you get a bubble with size one at x=3 and a bubble with size two at x=5.

I'd be super interested if you can find a way to do this dynamically without setting up a table to use for the x-axis.

[AlexisOlson]

This is easier if you create a calculated summary table like this:

From there, it's drag-and-drop to put the fields into a scatterplot.

If it needs to be more dynamic (where a calculated table doesn't work), you'll still need to create an extra table to use for the x-axis.

[Bart_Berg]

Thanks a lot for your detailed answer, it's very much appreciated! This does work indeed, but unfortunately I need to be able to filter on month even and even on a category which is in another dimension linked by content_id. What would my table structure need to look like to make that work?

Thanks :)!

[AlexisOlson · Accepted solution]

You'd need to create a table that contains all possible sessions per user.

Something like this:

X-Axis = 
VAR MaxSessions = MAXX ( ALL ( Sessions[user_id] ), CALCULATE ( COUNT ( Sessions[id] ) ) )
RETURN
    SELECTCOLUMNS ( GENERATESERIES ( 0, MaxSessions + 1 ), "Sessions", [Value] )

The measures have to be a bit more complicated.

AvgUniqueContent = 
VAR CurrSessions = SELECTEDVALUE ( 'X-Axis'[Sessions] )
VAR Summary =
    SUMMARIZE (
        Sessions,
        Sessions[user_id],
        "@UniqueContent", DISTINCTCOUNT ( Sessions[content_id] ),
        "@Sessions", COUNT ( Sessions[id] )
    )
RETURN
    AVERAGEX ( FILTER ( Summary, [@Sessions] = CurrSessions ), [@UniqueContent] )

The user count can be defined similarly (just use COUNTROWS instead of AVERAGEX in the last line).

[Bart_Berg]

That's awesome, thanks again for the detailed explanation. This works great!!