Excel Forum

Percentile calculation by group

giovanni zanocco

New Member

Members

Forum Posts: 1

Member Since:
May 18, 2023

Offline

May 18, 2023 - 2:50 am

Hi all,
I am struggling with the following problem: I have a sample data set where I need to calculate the n percentile (say 90% percentile) for records pertaining to a given Area (see screenshot). The percentile of each Area should then be reported in an additional column.
Hope the screenshot clarifies the problem.
Thank you
giovanni

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

May 25, 2023 - 2:42 pm

Hi Giovanni,
Try this query:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Grouped", (x)=> List.Sum(Table.SelectRows(Source, each _[Area] = x[Area])[Custom.Peso])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Percent", each [Custom.Peso]/[Grouped], Percentage.Type)
in
#"Added Custom1"

Forum Timezone: Australia/Brisbane

Most Users Ever Online: 245

Currently Online: Saliha Mohamed

Guest(s) 9

Currently Browsing this Page:
1 Guest(s)

Top Posters:

Catalin Bombea: 1939

SunnyKow: 1432

Anders Sehlstedt: 930

Purfleet: 415

Hans Hallebeek: 355

Frans Visser: 349

David_Ng: 312

lea cohen: 248

Jessica Stewart: 219

A.Maurizio: 216

Newest Members:

HMDIGPzunsnLJt lObJcycvbDLCmIq

Stephen Brown

John Kang'ara

iDeYYtyNRPTss icXlxLtkCZaWuY

Elizabeth Kinsey

Shaun Del Toro

Clive Webb

Forum Stats:

Groups: 3

Forums: 25

Topics: 7095

Posts: 31110

Member Stats:

Guest Posters: 49

Members: 34532

Moderators: 2

Admins: 3

Administrators: Mynda Treacy, Philip Treacy, Jessica

Moderators: Velouria, Riny van Eekelen

Percentile calculation by group|Power Query|Excel Forum|My Online Training Hub