topic Re: Top 10 in DAX Commands and Tips

Top 10

Guille2711410 — Sun, 26 Jul 2020 22:39:30 GMT

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

Re: Top 10

mahoneypat — Mon, 27 Jul 2020 01:45:59 GMT

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

Re: Top 10

amitchandak — Mon, 27 Jul 2020 02:24:21 GMT

Guille2711410 , Try like

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

Re: Top 10

Guille2711410 — Mon, 27 Jul 2020 03:06:48 GMT

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