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

4 Replies

  • 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

    • 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

  • Baskar's avatar
    Baskar
    Resident Rockstar
    10 years ago

    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