Forum Discussion
Solved
Repartition by max activity
Hi ! I've data like this : My goal is to have a pie shart to show the repartition of the members by their most used activity like this : I tried a lot of things to do this but I didn...
This works if you use columns from the dimension table rather than the activity table
Count members with main activity type = VAR CurrentType = SELECTEDVALUE ( Activities[type] ) VAR BaseTable = ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZE ( activity, Users[account], Activities[type] ), REMOVEFILTERS ( Activities[type] ) ), "@sum", CALCULATE (COUNTROWS( activity ) ) ) VAR PartitionedTable = INDEX ( 1, BaseTable, ORDERBY ( [@sum], DESC ), PARTITIONBY ( Users[account] ) ) VAR Types = GROUPBY ( PartitionedTable, Activities[type], "@users", SUMX ( CURRENTGROUP (), 1 ) ) VAR Result = SUMX ( FILTER ( Types, Activities[type] = CurrentType ), [@users] ) RETURN Result
johnt75
Super User
Super UserThis works if you use columns from the dimension table rather than the activity table
Count members with main activity type =
VAR CurrentType =
SELECTEDVALUE ( Activities[type] )
VAR BaseTable =
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( activity, Users[account], Activities[type] ),
REMOVEFILTERS ( Activities[type] )
),
"@sum", CALCULATE (COUNTROWS( activity ) )
)
VAR PartitionedTable =
INDEX (
1,
BaseTable,
ORDERBY ( [@sum], DESC ),
PARTITIONBY ( Users[account] )
)
VAR Types =
GROUPBY (
PartitionedTable,
Activities[type],
"@users", SUMX ( CURRENTGROUP (), 1 )
)
VAR Result =
SUMX ( FILTER ( Types, Activities[type] = CurrentType ), [@users] )
RETURN
Result
Yes ! It perfectly works ! (A little bit long with a big number of data, but not a problem)
Thank's a lot !
- mark_endicott
MichiyoTora - Glad johnt75 's approach worked, I misread your requirement so even if I fixed my DAX it would've given you a completely incorrect answer! Apologies.
