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
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.