Forum Discussion

Guille2711410's avatar
Guille2711410
Icon for Helper I rankHelper I
6 years ago

Top 10

Hello I would like to get the top 10 of my collected commissions, but I also want to prevent the number 1 from appearing, since it is not identified.

The formula I use is:

M_Top10 =

      VAR ComPorTipo =

           SUMMARIZE (

                COMT04,

                COMT04 [C76CODCOM],

               "TotCom", SUM (COMT04 [C76IMPRE01]) )

      VAR SumFiltered =

           TOPN(

                10,

                ComPorTipo,

                [TotCom]

            )

      RETURN SumFiltered

However it gives error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

I will appreciate your help for these two queries.

Thank you very much

 

3 Replies

    • Guille2711410's avatar
      Guille2711410
      Icon for Helper I rankHelper I
      6 years ago
      Excellent!!! All ok Thank you very much amitchandak and also for my previous query Guille
  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    This is an expression that returns a 10-row table, so it will error in a measure expression.  To get the list of your Top 10, you can use CONCATENATEX, but I am not sure if that is what you need.  You can change the Return in your expression to the following.

     

    Return CONCATENATEX(SumFiltered, COMT04 [C76CODCOM])

     

    If you want to omit the top 1, you can do a TOPN(9, SumFiltered, [TotCom], ASC), and then do CONCATENATEX on that table.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat