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
Icon for Microsoft Employee rankMicrosoft 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
Icon for Post Prodigy rankPost Prodigy
10 years ago

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