Repartition by max activity

[MichiyoTora]

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 !

[v-yangliu-msft]

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]

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.

[johnt75]

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]

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

[mark_endicott]

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

[MichiyoTora]

Thank's a lot for your time and your help.
Power BI found a problem with the partitionby part of your measure. I don't know well enough this option to correct it.
Here is a sample of my acticity data, you'll be able to create dimensions tables with values function : https://docs.google.com/spreadsheets/d/1RUHtEUY8Ywsi4vPLvJzk-vh6QSRg6HyOWSWz-PDfFMg/edit?usp=sharing

[johnt75 · Accepted solution]

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
	

[MichiyoTora]

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.