Topn Function.

[weilip1803]

Hi I want to know how to use the topN function. Currently I am having a count row measure and I only want to display the top 10 results of my group how do I do it?

[v-caliao-msft · Accepted solution]

Hi Weilip,

According to your description, you need to get the top 10 count for your group, right?

I have tested it on my local environment, the steps below are for you reference.

1. Create a calculated column in your original table using the expression below.
   Count=CALCULATE(COUNTA(Sheet1[Subgroup]),ALLEXCEPT(Sheet1,Sheet1[GroupName]))
2. Create a new table use the expression below
   TOP10 = TOPN(10,SUMMARIZE(Original,Original[GroupName]),[Measure])
3. Add a new column in new created table.
   Count = LOOKUPVALUE(Original[count],Original[GroupName],'TOP10'[GroupName])

Regards,

Charlie Liao

[seena]

TOP10 = TOPN(10,SUMMARIZE(Original,Original[GroupName]),[Measure])

Can the value ' 10' be dynamically selected?  ---topn[topnvalue]

If I try to replace it with [SelectedTopNNumber] where [SelectedTopNNumber]=value(topn[topnvalue]) , it throws me the below error,

MdxScript(Model) (17, 35) A table of multiple values was supplied where a single value was expected

[ElliotP]

@v-caliao-msftFantastic, Helped me out a lot!

[Baskar]

Hi weilip,

Do you want to show Top N Group by Count of Row measures Right ?

If yes please follow the below steps it will help u ...

1. Create new measure

Measure Count = count("Count row of Measures")

2. Create Rank Function

Rank = RankX(Allselected("Group"),Measure Count,,Asc,Dense)

3. Drag the rank field in Visual filter and choose what ever Top value u want.

let me know the feedback