Forum Discussion
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...
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).
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).
That's awesome, thanks again for the detailed explanation. This works great!!