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't succeed.
The difficulty is to show only the type of activity the most used by user (the total have to be the number of users, not take them in count several times in differents types), and it change with the selected date range in visual slicer, so a calculated column is not enought for this problem.

Can you help me please ? 
Thanks !

  • 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

9 Replies

  • 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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from mark_endicott  and johnt75  , please allow me to provide another insight: 
    Hi  MichiyoTora ,

    I can't open your PBIX, I created the sample data, you can try to create a measure using a virtual table to get the maximum count and then put it in a filter set to 1 to achieve.

    Here are the steps you can follow：

    1. Create measure.

     

    Flag =
var _mindate=MINX(ALLSELECTED('Table'),[date])
var _maxdate=MAXX(ALLSELECTED('Table'),[date])
var _table=
    FILTER(ALLSELECTED('Table'),[date]>=_mindate&&[date]<=_maxdate)
var _table2=
    ADDCOLUMNS(
    _table,"Count",COUNTX(FILTER(_table,[type]=EARLIER([type])),[account]))
var _test=
CONCATENATEX(
    FILTER(_table2,[Count]=MAXX(_table2,[Count])),[type],"-")
return
IF(
    CONTAINSSTRING(
        _test,MAX('Table'[type]))=TRUE(),1,0)

     

    2. Place [Flag]in Filters, set is=1, apply filter.

    3. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • MichiyoTora's avatar
      MichiyoTora
      Frequent Visitor
      1 year ago

      Hi !
      Thank's for your help. It's an approach I've already try.
      If I apply your proposition, it doesn't work because it shows only 1 type of activity (the most representative)
      In your sample of data for example, if the account 3 use "push" more than other activities types, we should have a pie chart with 7 team and 1 push, but we still have 8 team.