Creating a table that summarises records per week and with top performers

[scottcolbourne]

I need to create a visual or table like the following:

I have an activities table with multiple columns, including:

Date

Person

activityid

Plus a date table with week end dates in it linked to the above date.

Now it is easy to list number of activities per person, but to show the TOP N, only works on the total, not on each week's totals.

To get a table to similar to above:

Do I need a summary table?

Also how do you list multiple people on one line?

Any ideas would be great for how I can get the top 3 for each given week.

[Ashish_Mathur]

Hi,

Share the raw dataset in a format that can be pasted in an Excel workbook.  Alternatively, share the link from where i can download your PBI file. 

[mahoneypat · Accepted solution]

You can use TOPN and CONCATENATEX to get your result.  For example,

Top Performers =
VAR vTop3 =
    TOPN (
        3,
        VALUES ( Table[Person] ),
        CALCULATE (
            DISTINCTCOUNT ( Table[ActivityID] )
        ), DESC
    )
RETURN
    CONCATENATEX (
        vTop3,
        Table[Person],
        ", "
    )

Pat

[scottcolbourne]

Thank you Pat - that is great.  Strangely though it does not list them in descending order of number of activities.  The top 3 are correct though.  Any ideas why it is not ordering them in the concatenation in the descending order?