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

     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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's avatar
    Bart_Berg
    Frequent Visitor
    4 years ago

    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's avatar
      AlexisOlson
      Super User
      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).

       

      • Bart_Berg's avatar
        Bart_Berg
        Frequent Visitor
        4 years ago

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