TopN

[bhmiller89]

 I have a table SalesOpportunities that lists various information including "RecurringServices$," "Project$," and "Product$"

I want to calculate/show the Top 10 Sales Opportunities for 1. The highest Product$ 2. The highest RecurringServices$ and 3. The highest Project$

[BhaveshPatel]

You can write a DAX Query as below:

and modify further as per your needs.

EVALUATE

 TOPN(

        10,

       SalesOpportunities,

       SalesOpportunities[Product$],

       SalesOpportunities[RecurringServices$],

       SalesOpportunities[Project$]

)

Hope this would solve your problem.

[bhmiller89]

Hi,

I wrote

TopOpps= TOPN(10, 'Sales Opportunities', 'Sales Opportunities[Product$], DESC, 'Sales Opportunities[Project$], DESC, 'Sales Opportunities[Recurring$], DESC)

and I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[v-shex-msft · Accepted solution]

Hi bhmiller89,

>> and I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

I think you put this dax formula to a measure, you should use a table to receive the new table from the topn function:

TopN function: Returns the top N rows of the specified table.

The order by columns of topN function seems not work, perhaps you could try to use ‘sample function’:

Regards,

Xiaoxin Sheng

[BhaveshPatel]

You need to use the DAX Studio to get the query results as it is referring to the multiple columns. 

downloading is at 

https://www.sqlbi.com/tools/dax-studio/

use EVALUATE 

     QUERY SYNTAX

[BhaveshPatel]

TopOpps= CALCULATE([expression],TOPN(10, 'Sales Opportunities', 'Sales Opportunities[Product$], DESC, 'Sales Opportunities[Project$], DESC, 'Sales Opportunities[Recurring$], DESC)