Forum Discussion

weilip1803's avatar
weilip1803
Frequent Visitor
10 years ago
Solved

Topn Function.

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?
Need Help
Tutorial Requests
  • v-caliao-msft's avatar
    v-caliao-msft
    10 years ago

    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

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
10 years ago

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

  • ElliotP's avatar
    ElliotP
    Post Prodigy
    10 years ago

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

  • seena's avatar
    seena
    Frequent Visitor
    9 years ago

    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