Agregate most often text value

[JiriS]

Hi,

I need to create a table visualisation.

My data set looks:

Name	Personal number	Department	Hours	Day
Peter	1100	Department2	5	18.06.2020
Peter	1100	Department2	3	19.06.2020
Peter	1100	Department1	8	18.06.2020
Peter	1100	Department1	4	17.06.2020
Peter	1100	Department1	2	16.06.2020
Lucy	1212	Department3	5	16.06.2020
Lucy	1212	Department1	8	17.06.2020
Lucy	1212	Department3	5	18.06.2020
John	1515	Department2	2	18.06.2020

How should my dataset for visualisation look like:

Name	Personal number	Most often department by person	Hours	Date
Peter	1100	Department1	5	18.06.2020
Peter	1100	Department1	3	19.06.2020
Peter	1100	Department1	8	18.06.2020
Peter	1100	Department1	4	17.06.2020
Peter	1100	Department1	2	16.06.2020
Lucy	1212	Department3	5	16.06.2020
Lucy	1212	Department3	8	17.06.2020
Lucy	1212	Department3	5	18.06.2020
John	1515	Department2	2	18.06.2020

How can I agregate the department by most often value of each person?

Thank you for your ideas,

George

[Anonymous]

HI @JiriS,

Please explain more about your requirement and processing logic, it is hard to find out the rule for your sample data.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[JiriS]

I am sorry. I split the table rows by Name. Then I count the number of each department for the specific person. The most often value of department is than given in all rows of this person.

Do you understand or should I explaint it better?

Thank you

[Anonymous]

Hi @JiriS,

You can use the below calculate column formula to calculate the rank based on the count of person and department, then you can use this as sort order/filter on your visual.

Rank =
VAR _addcolumn =
    ADDCOLUMNS (
        'Table',
        "Merged", [Name] & "|" & [Most often department by person]
    )
VAR summary =
    SUMMARIZE (
        _addcolumn,
        [Merged],
        "Count", COUNTAX ( FILTER ( _addcolumn, [Merged] = EARLIER ( [Merged] ) ), [Merged] )
    )
VAR _current =
    MAXX (
        FILTER (
            summary,
            PATHITEM ( [Merged], 1 ) = EARLIER ( [Name] )
                && PATHITEM ( [Merged], 2 ) = EARLIER ( [Most often department by person] )
        ),
        [Count]
    )
RETURN
    COUNTROWS ( FILTER ( summary, [Count] >= _current ) )

Regards,

Xiaoxin Sheng

[Pragati11]

Hi @JiriS ,

Can you tell the logic for Most often value for each person? It is not clear to me.

Thanks,

Pragati