Show Top N values in Table

[Anonymous]

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!

[Phil_Seamark]

Hi @Anonymous

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

[Anonymous]

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]

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])

[Anonymous]

Hi Phil,

I receive:  The syntax for 'RETURN' is incorrect.   (DAX(.... your code string).

So yes, I would like the sum of the top 10 values and be able to display in a graph the "Completed By" names and values.

Thank you for your help by the way.

[Phil_Seamark · Accepted solution]

Are you adding it as a calculated column? (not a measure)?

And did you cut and paste all text, including brackets?

Here is a basic version of the data in a PBIX file you can download

https://1drv.ms/u/s!AtDlC2rep7a-oi4X3UW0fCQEVy16

[Anonymous]

You Rock!   Yes, the first try was my mistake!   That worked like a charm.  Thank you so much!   Really appreciate your help.

[Phil_Seamark]

Cool,

I did have a typo in the original reply where I used a 3 instead of a 10, but I hope you picked that up 🙂

I've edited the original reply to show a 10 now.

[Anonymous]

Your awesome.   I need to figure out how to cut and paste my Snagit screen captures in here.   This is the first forum I have participated in.