Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
10 years ago
Solved

TopN

 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$

Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    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

5 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    10 years ago

    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's avatar
      bhmiller89
      Helper V
      10 years ago

      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."