Forum Discussion

morgancampbell's avatar
morgancampbell
Icon for Helper I rankHelper I
6 years ago
Solved

Card visual for the top performer

I have data which is roughly as follows:

DateNameCall Time (Hours)
11/09/20Person 12.34
11/09/20Person 21.56
11/09/20Person 34.67
10/09/20Person 13.45
10/09/20Person 21.56
10/09/20Person 32.35
etcetcetc

 

And I'm looking to create a card visual to show the person who has the highest call time each week. I already have a page filter to show only the last week, which I assume will also apply to any calculations I add to a visual.

 

How would I go about creating a calculation that can do this?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi morgancampbell,

    You can try to use the following calculated table formula to summarize your raw table records based on the year and week group.
    Then it will output the top users based on total call hours and year week group:

    TC by Year & Week = 
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS ( T2, "Weeknum", WEEKNUM ( [Date], 1 ), "Year", YEAR ( [Date] ) ),
        [Name],
        [Year],
        [Weeknum],
        "TC", SUM ( T2[Call Time (Hours)] )
    )
RETURN
    ADDCOLUMNS (
        GROUPBY ( summary, [Year], [Weeknum], "MaxTC", MAXX ( CURRENTGROUP (), [TC] ) ),
        "Name",
            MAXX (
                FILTER (
                    summary,
                    [TC] = EARLIER ( [MaxTC] )
                        && [Year] = EARLIER ( [Year] )
                        && [Weeknum] = EARLIER ( [Weeknum] )
                ),
                [Name]
            )
    )


    Regards,

    Xiaoxin Sheng

2 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    6 years ago

    I thought there was a TOPN type of filter built in which you could use for this sort of thing?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi morgancampbell,

    You can try to use the following calculated table formula to summarize your raw table records based on the year and week group.
    Then it will output the top users based on total call hours and year week group:

    TC by Year & Week = 
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS ( T2, "Weeknum", WEEKNUM ( [Date], 1 ), "Year", YEAR ( [Date] ) ),
        [Name],
        [Year],
        [Weeknum],
        "TC", SUM ( T2[Call Time (Hours)] )
    )
RETURN
    ADDCOLUMNS (
        GROUPBY ( summary, [Year], [Weeknum], "MaxTC", MAXX ( CURRENTGROUP (), [TC] ) ),
        "Name",
            MAXX (
                FILTER (
                    summary,
                    [TC] = EARLIER ( [MaxTC] )
                        && [Year] = EARLIER ( [Year] )
                        && [Weeknum] = EARLIER ( [Weeknum] )
                ),
                [Name]
            )
    )


    Regards,

    Xiaoxin Sheng