Forum Discussion

cst_dev32's avatar
cst_dev32
Frequent Visitor
7 years ago
Solved

Display TOP N by Count base on 1 Field

Hy guys, i have an issue for my work I have 1 column named PLNT and I need to display top 5 from that column base on their count here's the data   And i filter by PLNT count   B...
Need Help
  • venug20's avatar
    venug20
    7 years ago

    cst_dev32

     

    You can achive this in two ways

     

    1st way: which already provide in above...

     

    1.  go to menu bar select "Edit Queries"

    2.  go add column "index column" 

    3.  select "From 1" in index column

     

    like below

     

     

    2nd way: (If not available power query editor)

     

    1. create one column for count

     

    index = CALCULATE(COUNT('Rank'[Plant]), FILTER('Rank', 'Rank'[Plant] = EARLIER('Rank'[Plant])))

     

    2. create quick measure for running total

     

    index running total in Plant =
    CALCULATE(
    SUM('Rank'[index]),
    FILTER(
    ALLSELECTED('Rank'[Plant]),
    ISONORAFTER('Rank'[Plant], MAX('Rank'[Plant]), DESC)
    )
    )

     

    3. take "index running total in Plant" into visual filter chose <6

     

     

     

     

    if it is solution for your query, please accept as solution...

     

     

venug20's avatar
venug20
Resolver I
7 years ago

cst_dev32

 

you can achive by this way... it may helps... you can try....

 

Steps:

 

1. create index column

2. take index column into visual filter

3. you can choose your number in filter... then apply

 

 

 

If it is solution to your query. Please accept as a soluiton... it will helps to others....

 

 

 

 

cst_dev32's avatar
cst_dev32
Frequent Visitor
7 years ago

Hy venug20

Thanks for reply my question

Can you tell me how to create the column index like that?

I just create the new calculate table like this

 

 

but i stuck on creating index like you did

can you show me the formula?

thanks

 

FYI : I use direct query, so i can't use edit queries

  • venug20's avatar
    venug20
    Resolver I
    7 years ago

    cst_dev32

     

    You can achive this in two ways

     

    1st way: which already provide in above...

     

    1.  go to menu bar select "Edit Queries"

    2.  go add column "index column" 

    3.  select "From 1" in index column

     

    like below

     

     

    2nd way: (If not available power query editor)

     

    1. create one column for count

     

    index = CALCULATE(COUNT('Rank'[Plant]), FILTER('Rank', 'Rank'[Plant] = EARLIER('Rank'[Plant])))

     

    2. create quick measure for running total

     

    index running total in Plant =
    CALCULATE(
    SUM('Rank'[index]),
    FILTER(
    ALLSELECTED('Rank'[Plant]),
    ISONORAFTER('Rank'[Plant], MAX('Rank'[Plant]), DESC)
    )
    )

     

    3. take "index running total in Plant" into visual filter chose <6

     

     

     

     

    if it is solution for your query, please accept as solution...