Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Show Top N values in Table

Hello, I am using Power BI Desktop with SSAS.   I have one table downloaded from a cube.

 

Table Name:  Intake.   I have an Intake Count and a Completed By column.   I am trying to create a column that will sum the top 10 "Completed By" people so I can display it using the top down filter visual.

 

I am having problems with the syntax.  I have reviewed articles re RankX, TopN, Summarize, etc. but I have not been able to nail down the syntax.   On one try I got a circular data issue.

 

Any help would be appreciated.

 

Thank you!

Need Help

8 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi Anonymous

     

    Do you have some sample data of what your table looks like?  

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I don't seem to be able to cut and paste a snapshot of the columns.   Does this help?

       

      TableName:      Intake

      Column1:          Intake Count      Whole Number

      Column2:         Completed By    Text

       

      I am displaying the data for one year.

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        Hi Anonymous

         

        This calculated column, when added to your Intake table, will show the sum total of the top 10 values by [Intake].  If this is not exactly what you need, let me know and I will tweak the calculation for you :)

         

        New Column = 
VAR myRank = TOPN(10,ALL('Intake'),[Intake Count],DESC)
RETURN SUMX(myRank,[Intake Count])