Only return top N rank by group

[Anonymous]

Hi,

I would like some guidance to return the top ranked record per group. For instance, from the following dataset:

Division	Team	Total	Player	Score
Premier	Spurs	26	Kane	9
Premier	Spurs	26	Son	11
Premier	Spurs	26	Lucas	6
Premier	Liverpool	19	Mane	11
Premier	Liverpool	19	Salah	8
Premier	Southampton	7	Ings	7
Premier	City	28	Sterling	7
Premier	City	28	DeBruyne	9
Premier	City	28	Aguero	12

The correct output would be:

Division	Team	Total	Player	Score
Premier	Spurs	26	Son	11
Premier	Liverpool	19	Mane	11
Premier	Southampton	7	Ings	7
Premier	City	28	Aguero	12

Note that Total is the sum of Score by Team. Ideally, I would like to retain the Total summary if possible, but only display one record by Team with Max Score value.

Is this possible?

[Icey · Accepted solution]

Hi @Anonymous ,

Please check:

Measure =
VAR t =
    FILTER (
        TOPN (
            1,
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Team] = MAX ( 'Table'[Team] ) ),
            [Score], DESC
        ),
        [Score] = MAX ( [Score] )
    )
RETURN
    SUMX ( t, [Score] )

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Icey

Thanks for your effort on this. I have implemented the solution as prescribed,

TopUser =

VAR t =

    FILTER (

        TOPN (

            1,

            FILTER ( ALLSELECTED ( 'Tableau Logs' ), 'Tableau Logs'[Tableau Project Long Key] = MAX ( 'Tableau Logs'[Tableau Project Long Key] ) ),

            [# of Visits (Project)], DESC

        ),

        [# of Visits (Project)] = MAX ( [# of Visits (Project)] )

    )

RETURN

    SUMX ( t, [# of Visits (Project)] )

however, I get the error:

Can't Display the Visual - The Report measure 'Tableau Logs'[Top User] has a syntax or semantic error at line 9, position 35, reported by Analysis Services: 'The MAX function only accepts a column reference as the argument number 1.'.

Any idea what this means in this context, and what to do to overcome it?

Thanks

[Icey]

Hi @Anonymous ,

Since [# of Visits (Project)] is a measure, it is needed to use MAXX instead of MAX. Try this:

TopUser =
VAR t =
    FILTER (
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( 'Tableau Logs' ),
                'Tableau Logs'[Tableau Project Long Key]
                    = MAX ( 'Tableau Logs'[Tableau Project Long Key] )
            ),
            [# of Visits (Project)], DESC
        ),
        [# of Visits (Project)] = MAXX ( 'Tableau Logs', [# of Visits (Project)] )
    )
RETURN
    SUMX ( t, [# of Visits (Project)] )

If this is still not work, please share us a dummy .pbix file for test. Please remove sensitive information.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Icey. Much appreciated! 😀

[Anonymous]

By the way, I would appreciate any noob-proofed responses, as I am very new to Power BI! 🙂