Top 10

[Guille2711410]

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

[amitchandak]

@Guille2711410 , Try like

Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

[Guille2711410]

Excellent!!! All ok Thank you very much amitchandak and also for my previous query Guille

[mahoneypat]

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