Forum Discussion

MichiyoTora's avatar
MichiyoTora
Frequent Visitor
1 year ago
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...
  • johnt75's avatar
    johnt75
    1 year ago

    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's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

You can try

Count members with main activity type =
VAR CurrentType =
    SELECTEDVALUE ( activity[type] )
VAR BaseTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE ( activity, activity[account], activity[type] ),
            REMOVEFILTERS ( activity[type] )
        ),
        "@sum", CALCULATE ( SUM ( activity[number] ) )
    )
VAR PartitionedTable =
    INDEX (
        1,
        BaseTable,
        ORDERBY ( [@sum], DESC ),
        PARTITIONBY ( activity[account] )
    )
VAR Types =
    GROUPBY (
        PartitionedTable,
        activity[type],
        "@users", SUMX ( CURRENTGROUP (), 1 )
    )
VAR Result =
    SUMX ( FILTER ( Types, activity[type] = CurrentType ), [@users] )
RETURN
    Result
  • MichiyoTora's avatar
    MichiyoTora
    Frequent Visitor
    1 year ago

    It seems not working, members still in each type of activity they use, not only the principal one ...

    • mark_endicott's avatar
      mark_endicott
      Icon for Super User rankSuper User
      1 year ago

      MichiyoTora - it would be useful to have some sample data to test but I would structure this like so:

      Create a measure called Number of Activity:

      SUM( activity[number] )

       

      Then reference it in an additional measure:

       

      VAR RankedActivities =
    ADDCOLUMNS (
       SUMMARIZE ( activity, activity[account], activity[type] ),
        "ActivityRank",
            RANK (
                DENSE,
                ALLSELECTED ( activity[type] ),
                ORDERBY([Number of Activities], DESC),
                ,
                PARTITIONBY(activity[account])
            )
    )
VAR TopActivity =
    FILTER ( RankedActivities, [ActivityRank] = 1 )
RETURN
    CALCULATE (
        [Number of Activities],
        TREATAS ( SELECTCOLUMNS ( TopActivity, "account", activity[account] ), TD_members[account] )
    )

       

      There may be an issue with Ranking in this way inside an add columns, but if you can provide some sample data it would be easier to resolve. 

       

      Of course if this works, please accept as the solution. It helps with visibility for others with the same challenge!