Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Only return top N rank by group

Hi,

 

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

DivisionTeamTotalPlayerScore
PremierSpurs26Kane9
PremierSpurs26Son11
PremierSpurs26Lucas6
PremierLiverpool19Mane11
PremierLiverpool19Salah8
PremierSouthampton7Ings7
PremierCity28Sterling7
PremierCity28DeBruyne9
PremierCity28Aguero12

 

The correct output would be:

DivisionTeamTotalPlayerScore
PremierSpurs26Son11
PremierLiverpool19Mane11
PremierSouthampton7Ings7
PremierCity28Aguero12

 

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?

 

Group By
Max
Need Help
rank
  • Icey's avatar
    Icey
    5 years ago

    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.

5 Replies

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Icey. Much appreciated! ğŸ˜€

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Icey
        Community Support
        5 years ago

        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's avatar
    Anonymous
    Not applicable
    5 years ago

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