cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Calculating Top 1%, 2%, 3% , 4%,5% of the total row count based on a column max value

Hi Guys,

I have a table which contain 50,000 rows for which I need to create a custom column which calculates the Top 1%, 2%, 3%, 4%, 5% of  the total row count based on a column score which got max value.

e.g. 1% of 50,000 = 500, so the based on column score which got max value need to calculate 500 rows as "Top 1%".

Not sure if I can use Rank or Top N to acheive this.

Appreciate you guys help

Super User

@chiru5262 , make sure you have a unique ID column, or add an index column in the power query

divide(countx(filter(Table, [Index] <= earlier([Index]) ), [Index]) , countrows(Table) )

or

roundup(divide(countx(filter(Table, [Index] <= earlier([Index]) ), [Index]) , countrows(Table) ), -1)

Index column- Power Query- Index Column: https://youtu.be/NS4esnCDqVw

For measure refer

Pareto Analysis Again, 80% of sales, Order by Measure when REL position is used: https://youtu.be/GpoITi_tRIw

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.