Forum Discussion

Bart_Berg's avatar
Bart_Berg
Frequent Visitor
4 years ago
Solved

Group by and count in scatter plot

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 follow...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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).

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
4 years ago

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's avatar
AlexisOlson
Super User
4 years ago

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.