cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 3
Highlighted
Super User VI
Super User VI

Re: Top 10

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Super User IX
Super User IX

Re: Top 10

@Guille2711410 , Try like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Top 10

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors